Oracle Flashback Drop

Summary

Oracle 10g introduced a flashback drop feature that will allow you to bring back a dropped table without having to execute any type of recovery operation. This is due to the recyclebin which is what makes the flashback drop feature work. This feature is very useful for those times when a developer comes in who just dropped a table by mistake. Before flashback drop you either had to have an export of the table or recover the database using an auxiliary instance to get the table back and then move the table from the auxiliary instance back into database where the table was dropped. You can however drop a table and bypass the recycle bin as well by using the PURGE option to the drop table.


Some important things to note about the flashback drop feature

1. Think of each schema getting its own Recycle Bin
2. The recycle bin is a logical collection of previously DROPPED objects
3. In Oracle 10g the default action of a DROP TABLE command is to RENAME the table essentially placing the table in the RECYCLE BIN.
4. Objects in the RECYCLE BIN are NOT automatically purged and must be explicitly purged explicitly by a User or DBA action.
5. When a table is dropped the segments that table consumed in the tablespace are still present. Therefore when a table is dropped space is not freed as the table segments are still there.
6. If an object is dropped and recreated multiple times all dropped versions will be kept in the recycle bin, subject to space.
7. Where multiple versions are present, it's best to reference the tables via the table name shown in the RECYCLEBIN -> RECYCLEBIN_NAME.
8. For any references to the ORIGINAL_NAME, it is assumed the most recent object is drop version in the referenced question.


Showing the contents of the RECYCLEBIN

Show the current schemas recyclebin
show recyclebin
select * from recyclebin ;

Show the DBA Recyclebin which will show all schema recyclebins
select * from dba_recyclebin ;


Purging the RECYCLEBIN

Purge current schema RECYCLEBIN
PURGE RECYCLEBIN ;

Purge All RECYCLEBINs
PURGE DBA_RECYCLEBIN ;

Purge Objects from RECYCLEBIN
PURGE TABLE tablename ;
PURGE INDEX indexname ;

Purge Objects from RECYCLEBIN for a tablespace
PURGE TABLESPACE ts_name ;
PURGE TABLESPACE ts_name USER username ;


Dropping the table and bypass the RECYCLEBIN

DROP TABLE tablename PURGE ;


Recovering a Table from the RECYCLEBIN

Recover last dropped version of the table
FLASHBACK TABLE tablename TO BEFORE DROP;

Recover the table, but give it a new name
FLASHBACK TABLE tablename TO BEFORE DROP RENAME TO newtablename ;

Recover the table from a prior dropped version of the table
FLASHBACK TABLE “recyclebin name” TO BEFORE DROP ;
FLASHBACK TABLE “recyclebin name” TO BEFORE DROP RENAME TO newtablename ;


Examples Using Flashback Drop

1. Create a table for our testing
SQL> CREATE TABLE flashback_drop_test ( id NUMBER(10) );

Table created.


2. Insert a record so that we have data
SQL> INSERT INTO flashback_drop_test (id) VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.


3. Drop the table
SQL> DROP TABLE flashback_drop_test;

Table dropped.


4. Show the RECYCLEBIN
SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_TEST BIN$qLfTuNLERa6PzKkR4l1luA==$0 TABLE 2008-05-20:16:03:36


5. Show that we can no longer select from the table.
SQL> select * from flashback_drop_test;
select * from flashback_drop_test
*
ERROR at line 1:
ORA-00942: table or view does not exist


6. Bring back the last dropped version of the table
SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

Flashback complete.


7. Show that the table is back and we can select from it
SQL> select * from flashback_drop_test;

ID
----------
1


8. Show that the RECYCLEBIN is now empty
SQL> show recyclebin
SQL>


9. Drop the table again using the purge option.
SQL> drop table flashback_drop_test PURGE ;

Table dropped.

10. Show that the recyclebin is empty as we used the PURGE option.
SQL> show recyclebin
SQL>


11. Recreate the table for our further testing.
SQL> CREATE TABLE flashback_drop_test ( id NUMBER(10) );

Table created.


12. Drop the table again using the purge option.
SQL> drop table flashback_drop_test ;

Table dropped.


13. recreate the table to have multiple versions of the table.
SQL> CREATE TABLE flashback_drop_test ( id NUMBER(10) );

Table created.


14. Insert a record so that we have data so we will have 2 versions of the table one with a record and one empty.
SQL> INSERT INTO flashback_drop_test (id) VALUES (1);

1 row created.

SQL> COMMIT;


15. Show that we have the table in the RECYCLEBIN again.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_TEST BIN$4/+GRpvFSl6cIWL21YkgPA==$0 TABLE 2008-05-20:16:03:39


16. Show that the we can select directly from the table in the RECYCLEBIN using the RECYCLEBIN name and show that the table in the RECYCLEBIN has no rows.
SQL> select * from "BIN$XzZkNWkNQBiwl9WSVWk49w==$0" ;

no rows selected


17. Show that the current table still exists and has rows
SQL> select * from flashback_drop_test ;

ID
----------
1


18. Drop the table
SQL> drop table flashback_drop_test ;

Table dropped.


19. Show that we now have 2 versions of the table in the recyclebin.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_TEST BIN$3FoAb84NSMmiVnhYco2e5w==$0 TABLE 2008-05-20:16:50:25
FLASHBACK_DROP_TEST BIN$XzZkNWkNQBiwl9WSVWk49w==$0 TABLE 2008-05-20:16:46:32


20. Bring back the latest version of the table but with another table name.
SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Flashback complete.


21. Show that we can select from the new table name.
SQL> select * from flashback_drop_test_old;

ID
----------
1


22. Show that we still do not see the original table.
SQL> select * from flashback_drop_test;
select * from flashback_drop_test
*
ERROR at line 1:
ORA-00942: table or view does not exist


23. Show that we still have a version of the table in the RECYCLEBIN
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_TEST BIN$XzZkNWkNQBiwl9WSVWk49w==$0 TABLE 2008-05-20:16:46:32


24. Create a new version of the table.
SQL> CREATE TABLE flashback_drop_test ( id NUMBER(10) );

Table created.


25. Insert a row so this version has a record.
SQL> INSERT INTO flashback_drop_test (id) VALUES (1);

1 row created.

SQL> COMMIT;


26. Drop the table
SQL> drop table flashback_drop_test ;

Table dropped.


27. Show that the 2 versions are in the RECYCLEBIN
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_TEST BIN$MMV1E9cRRZCvUkC8HM3WhQ==$0 TABLE 2008-05-20:16:58:45
FLASHBACK_DROP_TEST BIN$XzZkNWkNQBiwl9WSVWk49w==$0 TABLE 2008-05-20:16:46:32


28. Restore the older version of the table from the RECYCLEBIN, this one does not have a record in it.
SQL> FLASHBACK TABLE "BIN$XzZkNWkNQBiwl9WSVWk49w==$0" TO BEFORE DROP ;

Flashback complete.


29. Show that the table is the correct version as it will have no record in it.
SQL> select * from flashback_drop_test ;

no rows selected