Recycle bin is one of the new feature of Oracle 10g. This feature works a little bit like the familiar Windows recycle bin or Mac Trash. Dropped tables go "into" the recyclebin, and can be restored from the recyclebin. There are two recyclebin views: USER_RECYCLEBIN and DBA_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.
Enable the database recyclebin:
1. The recycle bin is enabled by default.
SQL> ALTER SYSTEM SET recyclebin = ON;
2. To disable for the entire database (not recommended):
SQL> ALTER SYSTEM SET recyclebin = OFF;
3. To enable the recycle bin for a session:
SQL> ALTER SESSION SET recyclebin = ON;
4. To disable the recycle bin for a session:
SQL> ALTER SESSION SET recyclebin = OFF;
Show recyclebin contents:
1. To see the objects in the recyclebin:
SHOW RECYCLEBIN
Clear recyclebin:
1. To remove all dropped objects from the recyclebin (current user):
PURGE RECYCLEBIN;
2. To remove all dropped objects from the recyclebin (system wide):
PURGE DBA_RECYCLEBIN;
3. Tables can also be droped without sending them to the recyclebin. Example:
DROP TABLE t1 PURGE;
Examples:
1. Drop a table:
SQL> DROP TABLE t1;
2. Undrop the table:
SQL> FLASHBACK TABLE t1 TO BEFORE DROP;