Performing a Flashback Drop Operation
In Oracle Ver. 10g Oracle introduced the concept of Recycle Bin i.e. whatever tables you drop the database does not immediately remove the space used by table. Instead, the table is renamed and placed in Recycle Bin. The FLASHBACK TABLE…BEFORE DROP command will restore the table.
Oracle renames dropped and their associated objects, giving them system-generated recyclebin names that begin with BIN$.
BIN$unique_id$version
where:
- unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
- version is a version number assigned by the database
This feature is not dependent on UNDO TABLESPACE so UNDO_RETENTION parameter has no impact on this feature.
To Recover Drop tables the RECYCLEBIN parameter must be turned ON, otherwise you will not be able to recover drop tables
SQL> SELECT Value FROM V$parameter WHERE Name = 'recyclebin';
VALUE
---------------
on
SQL>
if the Value Is “on” then recyclebin feature is enabled for the Database.
If the Value is “off” the recyclebin feature is disabled.
SQL > ALTER SYSTEM SET recyclebin = ON;
or
SQL > ALTER SESSION SET recyclebin = ON;
SQL > ALTER SYSTEM SET recyclebin = OFF;
or
SQL > ALTER SESSION SET recyclebin = OFF;
Show the Contents in RECYCLEBIN
SQL > SHOW RECYCLEBIN;
Or
SQL > SELECT * FROM USER_RECYCLEBIN;
or
SQL> SELECT * FROM DBA_RECYCLEBIN;
Restore the Objects:
User can restore the Dropped tables by issuing the following commands,The following Commands can be used to restore the dropped Objects.
SQL > FLASHBACK TABLE <<Table_Name >> TO BEFORE DROP;
SQL> select * from test;
ID NAME
---------- --------------------------------------------------
1 D
2 Z
SQL>
SQL> drop table test;
Table dropped.
SQL> SHOW RECYCLEBIN;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$Y6TCpiPVE1jgVQAAAAAAAQ==$0 TABLE 2018-01-26:06:54:40
SQL>
SQL> FLASHBACK TABLE TEST TO BEFORE DROP;
Flashback complete.
SQL> select * from user_recyclebin;
no rows selected
SQL> select * from test;
ID NAME
---------- --------------------------------------------------
1 D
2 Z
SQL>
It is possible to restore the table in to different name by issuing the following SQL Command.
SQL > FLASHBACK TABLE << Dropped Table Name >> TO BEFORE DROP RENAME TO <<New Table Name >>;
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;
SQL> select * from test;
ID NAME
---------- --------------------------------------------------
1 D
2 Z
3 A
SQL> drop table test;
Table dropped.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$Y6TCpiPWE1jgVQAAAAAAAQ==$0 TABLE 2018-01-26:06:59:46
SQL> FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;
Flashback complete.
SQL> select * from test1;
ID NAME
---------- --------------------------------------------------
1 D
2 Z
3 A
Using the above statement, its possible to restore the various version of the table data if the table
is created and Dropped more than once.
While restoring system restores the table in Descending order.
SQL> show recyclebin
SQL>
SQL> CREATE TABLE TEST_RBIN (COL1 NUMBER);
Table created.
SQL> INSERT INTO TEST_RBIN VALUES (1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> DROP TABLE TEST_RBIN;
Table dropped.
SQL> CREATE TABLE TEST_RBIN (COL1 NUMBER);
Table created.
SQL> INSERT INTO TEST_RBIN VALUES (2);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> DROP TABLE TEST_RBIN;
Table dropped.
SQL> CREATE TABLE TEST_RBIN (COL1 NUMBER);
Table created.
SQL> INSERT INTO TEST_RBIN VALUES (3);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> DROP TABLE TEST_RBIN;
Table dropped.
SQL> SHOW RECYCLEBIN;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST_RBIN BIN$Y6TCpiPZE1jgVQAAAAAAAQ==$0 TABLE 2018-01-26:07:05:02
TEST_RBIN BIN$Y6TCpiPYE1jgVQAAAAAAAQ==$0 TABLE 2018-01-26:07:04:22
TEST_RBIN BIN$Y6TCpiPXE1jgVQAAAAAAAQ==$0 TABLE 2018-01-26:07:03:45
SQL> FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN1;
Flashback complete.
SQL> FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN2;
Flashback complete.
SQL> FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN3;
Flashback complete.
SQL> SELECT * FROM TEST_RBIN1;
COL1
----------
3
SQL> SELECT * FROM TEST_RBIN2;
COL1
----------
2
SQL> SELECT * FROM TEST_RBIN3;
COL1
----------
1
SQL>
Purging Objects from Recycle Bin
f you want to recover the space used by a dropped table give the following command
SQL> purge table emp;
If you want to purge objects of logon user give the following command
SQL> purge recycle bin;
If you want to recover space for dropped objects of a particular tablespace give the command
SQL> purge tablespace hr;
You can also purge only objects from a tablespace belonging to a specific user, using the following form of the command:
SQL> PURGE TABLESPACE hr USER scott;
If you have the SYSDBA privilege, then you can purge all objects from the recycle bin, regardless of which user owns the objects, using this command:
SQL> PURGE DBA_RECYCLEBIN;
To view the contents of Recycle Bin give the following command
SQL> show recycle bin;
Permanently Dropping Tables
If you want to permanently drop tables without putting it into Recycle Bin, drop tables with purge command like this
SQL> drop table emp purge;
This will drop the table permanently and it cannot be restored.
if table have indexes
SQL> drop table test;
Table dropped.
SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------------- -------------------------
BIN$Y6TCpiPcE1jgVQAAAAAAAQ==$1 BIN$Y6TCpiPaE1jgVQAAAAAAAQ==$0 INDEX
BIN$Y6TCpiPdE1jgVQAAAAAAAQ==$0 TEST TABLE
SQL>
SQL> flashback table test to before drop;
Flashback complete.
SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from user_recyclebin;
no rows selected
SQL> select owner,index_name,table_name,status from dba_indexes where owner='DUCAT';
OWNER INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ ---------- --------
DUCAT BIN$Y6TCpiPcE1jgVQAAAAAAAQ==$1 TEST VALID
SQL> alter index "BIN$Y6TCpiPcE1jgVQAAAAAAAQ==$1" rename to test_id_idx;
Index altered.
SQL> select owner,index_name,table_name,status from dba_indexes where owner='DUCAT';
OWNER INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ ---------- --------
DUCAT TEST_ID_IDX TEST VALID
SQL>
Related Views
- RECYCLEBIN$ (base table)
- DBA_RECYCLEBIN
- USER_RECYCLEBIN
- RECYCLEBIN (synonym for USER_RECYCLEBIN)
