Oracle Flashback Drop
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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

col name format a30
col value format a30

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

desc recyclebin

desc use_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 / as sysdba

ALTER SYSTEM SET recyclebin=off SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;

conn uwclass/uwclass

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 <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 / as sysdba

GRANT unlimited tablespace TO uwclass;

conn uwclass/uwclass

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 / 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

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-2014 Daniel A. Morgan All Rights Reserved