Recycle bin

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

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

To see the objects in the recyclebin:

SHOW RECYCLEBIN

Alternatively make a select:

select * from user_recyclebin;

Clear recyclebin

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

Drop a table:

SQL> DROP TABLE t1;

Undrop the table:

SQL> FLASHBACK TABLE t1 TO BEFORE DROP;

Also see

• Flashback - Oracle's flashback technology.
• Using Oracle's recycle bin