Oracle Flashback Drop
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Data Dictionary Objects
DBA_RECYCLEBIN RECYCLEBIN$ USER_RECYCLEBIN
RECYCLEBIN    
Related Startup Parameters col name format a30
col value format a30

SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';
System Privileges
PURGE DBA_RECYCLEBIN    
 
Recyclebin Management
Starting and stopping the recyclebin ALTER SYSTEM SET recyclebin=<OFF | ON> SCOPE=<BOTH | MEMORY | SPFILE>;
conn uwclass/uwclass@pdbdev

col name format a30
col value format a30

SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';

desc recyclebin

desc user_recyclebin

desc dba_recyclebin

SELECT object_name, original_name
FROM user_recyclebin;

CREATE TABLE t1 (
testcol DATE);

DROP TABLE t1;

SELECT object_name, original_name
FROM user_recyclebin;

conn sys@pdbdev as sysdba

ALTER SYSTEM SET recyclebin=off SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;

conn uwclass/uwclass@pdbdev

SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';

CREATE TABLE t2 (
testcol DATE);

DROP TABLE t2;

SELECT object_name, original_name
FROM user_recyclebin;

conn / as sysdba

ALTER SYSTEM SET recyclebin=on SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;
 
Flashback Dropped Objects
Flashback from the Recycle Bin using the Recyclebin object name FLASHBACK TABLE <schema_name.object_name> TO BEFORE DROP [RENAME TO <new_table_name>];
PURGE RECYCLEBIN;

SELECT object_name, original_name, type, related, base_object
FROM user_recyclebin;

CREATE TABLE t (
col1 NUMBER(3),
col2 VARCHAR2(3));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (col1)
USING INDEX;

ALTER TABLE t
ADD CONSTRAINT cc_t_col2
CHECK (col2 IN ('ABC', 'DEF'));

CREATE OR REPLACE TRIGGER bi_t
BEFORE INSERT
ON t
DECLARE
BEGIN
  NULL;
END bi_t;
/

DROP TABLE t;

SELECT object_name, original_name, type
FROM user_recyclebin;

FLASHBACK TABLE t TO BEFORE DROP;

SELECT object_name, original_name, type
FROM user_recyclebin;

SELECT table_name FROM user_tables;

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'T';

SELECT index_name, uniqueness
FROM user_indexes
WHERE table_name = 'T';

SELECT trigger_name
FROM user_triggers
WHERE table_name = 'T';

ALTER TABLE t RENAME CONSTRAINT "BIN$A08sxN1NQIGxVdIaARKePA==$0"
TO pk_t;

ALTER TABLE t RENAME CONSTRAINT "BIN$KKMjMoJYRLu7A2ugWCmnPQ==$0"
TO cc_t_col2;

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'T';

ALTER INDEX "BIN$lmaYVjyCS/2DrZpLpjJIcQ==$0" RENAME TO pk_t;

SELECT index_name, uniqueness
FROM user_indexes
WHERE table_name = 'T';

ALTER TRIGGER "BIN$kDcWB1a6Sa2jJL7zZVPdzQ==$0" RENAME TO bi_t;

SELECT trigger_name
FROM user_triggers
WHERE table_name = 'T';

DROP TABLE t PURGE;

SELECT object_name, original_name, type
FROM user_recyclebin;
Flashback from the Recycle Bin using the original object name FLASHBACK TABLE <original_table_name> TO BEFORE DROP {RENAME TO <new_table_name>};
CREATE TABLE test (
testcol  VARCHAR2(20));

INSERT INTO test VALUES ('ABC');

COMMIT;

SELECT * FROM test;

DROP TABLE test;

CREATE TABLE test (
testcol  VARCHAR2(20));

INSERT INTO test VALUES ('DEF');

COMMIT;

SELECT * FROM test;

DROP TABLE test;

CREATE TABLE test (
testcol  VARCHAR2(20));

INSERT INTO test VALUES ('GHI');

COMMIT;

SELECT * FROM test;

DROP TABLE test;

SELECT object_name, original_name, droptime
FROM recyclebin;

FLASHBACK TABLE test TO BEFORE DROP;

FLASHBACK TABLE test TO BEFORE DROP RENAME TO test2;

FLASHBACK TABLE test TO BEFORE DROP RENAME TO test1;

SELECT * FROM test;

SELECT * FROM test2;

SELECT * FROM test1;
 
Recyclebin Purging
Remove A Recycle Bin Object By Name PURGE TABLE "<recycle_bin_name>";
conn sys@pdbdev as sysdba

GRANT unlimited tablespace TO uwclass;

conn uwclass/uwclass@pdbdev

SELECT tablespace_name
FROM user_tablespaces;

CREATE TABLE test1
TABLESPACE uwdata AS
SELECT * FROM user_objects;

CREATE TABLE test2
TABLESPACE example AS
SELECT * FROM user_objects;

CREATE TABLE test3
TABLESPACE users AS
SELECT * FROM user_objects;

SELECT object_name, original_name
FROM user_recyclebin;

DROP TABLE test1;
DROP TABLE test2;
DROP TABLE test3;

SELECT object_name, original_name, ts_name
FROM user_recyclebin;

PURGE TABLE "BIN$V3zj9aOWRImOT89aKyyVSg==$0";

SELECT object_name, original_name, ts_name
FROM user_recyclebin;

PURGE TABLE TEST1;

SELECT object_name, original_name, ts_name
FROM user_recyclebin;

conn sys@pdbdev as sysdba

REVOKE unlimited tablespace FROM uwclass;
Remove Recycle Bin Objects By Tablespace PURGE TABLESPACE <tablespace_name>;
CREATE TABLE test1
TABLESPACE uwdata AS
SELECT * FROM user_objects;

CREATE TABLE test3
TABLESPACE example AS
SELECT * FROM user_objects;

DROP TABLE test1;
DROP TABLE test3;

SELECT object_name, original_name, ts_name
FROM user_recyclebin;

PURGE TABLESPACE
example;

SELECT object_name, original_name, ts_name
FROM user_recyclebin;
Remove Recycle Bin Objects By Tablespace And User PURGE TABLESPACE <tablespace_name> USER <schema_name>;
PURGE TABLESPACE uwdata USER uwclass;
Empty The Recycle Bin PURGE recyclebin;
Empty Everything In All Recycle Bins PURGE dba_recyclebin;

Related Topics
Flashback Archive
Flashback Database
Flashback Query
Flashback Table
Flashback Transaction
Table Version
Startup Parameters
Table
Undo Tablespace
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx