Recycle bin

From Oracle FAQ
Jump to: navigation, search

Oracle maintains a recycle bin for dropped objects starting with Oracle 10g. Dropped tables go "into" the recyclebin, and can be restored (undropped) from the recyclebin.

Enable the database recyclebin[edit]

The recycle bin is enabled by default.

SQL> ALTER SYSTEM SET recyclebin = ON;

To disable for the entire database (not recommended):

SQL> ALTER SYSTEM SET recyclebin = OFF;

To enable the recycle bin for a session:

SQL> ALTER SESSION SET recyclebin = ON;

To disable the recycle bin for a session:

SQL> ALTER SESSION SET recyclebin = OFF;

Show recyclebin contents[edit]

To see the objects in the recyclebin:

SHOW RECYCLEBIN

Alternatively make a select:

select * from user_recyclebin;

Clear recyclebin[edit]

To remove all dropped objects from the recyclebin (current user):

PURGE RECYCLEBIN;

To remove all dropped objects from the recyclebin (system wide, available to SYSDBA only or, starting with version 12c, to users having the PURGE DBA_RECYCLEBIN system privilege):

PURGE DBA_RECYCLEBIN;

Tables can also be dropped without sending them to the recyclebin. Example:

DROP TABLE t1 PURGE;

Tables inside recycle bin can be purged individually. Example:

PURGE TABLE t1;

Examples[edit]

Drop a table:

SQL> DROP TABLE t1;

Undrop the table:

SQL> FLASHBACK TABLE t1 TO BEFORE DROP;

Also see[edit]