[Oracle] Using the Recycle Bin with Oracle

When you drop a table, it remains in the recycle bin until space pressure from new objects or new rows in a table requires the reuse of the space. The indexes associated with the dropped tables also remain in the recycle bin.

The recycle bin is a data dictionary table that keeps track of dropped objects. The objects themselves still exist in the same location within the tablespace, but they are renamed.

The recycle bin is enabled by default.

RECYCLEBIN is a dynamic initialization parameter :

SQL> alter session set recyclebin=off;

SQL> alter system set recyclebin=on scope=spfile;

To query the recycle bin, you can use the data dictionary view USER_RECYCLEBIN. You can view purged objects for all users using the view DBA_RECYCLEBIN.

SQL> select owner, object_name, original_name, type, ts_name, droptime, can_undrop from dba_recyclebin;

You can purge the entire contents of the recycle bin with the PURGE RECYCLEBIN command.

• Restoring tables from the recycle bin

To restore a table from the recycle bin, you use the FLASHBACK TABLE .. TO BEFORE DROP command. If you specify the original table name in the command, the most recently dropped version of the table and its dependent objects are restored.

SQL> flashback table "BIN$**************" to before drop;

You can use the RENAME TO clause to give the restored table a new name

SQL> flashback table table_name to before drop rename to old_table_name;

• purging the recycle bin

The space in the recycle bin is managed automatically by Oracle. Older objects in the recycle bin are removed before new objects when free space is low. You can also manually remove objects from it by using the PURGE command.

SQL> show recyclebin:

SQL> purge table "BIN$************";

You can also purge only objects in the recycle bin from a specific tablespace or a specific user.

SQL> purge tablespace users;

SQL> purge tablespace app user test;

You can bypass the recycle bin when you drop a table by appending PURGE to the DROP TABLE command.

• querying the recycle bin

You can still use a SELECT statement to access the dropped table.

SQL> desc "BIN$***********"

SQL> select * from "BIN$***********";