10g Recycle Bin

Oracle 10g Recycle Bin is a virtual container to hold dropped objects until owner or DBA purges them out or when no more free space left in its tablespace which needs to save more data or a new object.

Default recyclebin is ON. After a table or index is dropped, you will see a new table or index with a long name like BIN$% under current schema. As some systems created and dropped tables very often "by design", in 10g this may bother some users to see many BIN$% tables which are not able to be dropped by using "drop table" command".

The following are something need to know about it:

1. Check recycle bin

SQL> show recyclebin;
SQL> select * from recyclebin;
SQL> select * from user_recyclebin;
SQL> select * from dba_recyclebin; --with DBA privilege

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
SEQ_TELE BIN$NEZqOOkqZdrgRAADung28g==$0 TABLE 2007-07-02:06:37:26
TORCMPLIST BIN$NH0oo3EvagvgRAADung28g==$0 TABLE 2007-07-04:23:56:05
TORCMPLIST BIN$NGnVEBsMIJ/gRAADung28g==$0 TABLE 2007-07-04:00:52:42
TORCMPLIST BIN$NFlS+mOZHurgRAADung28g==$0 TABLE 2007-07-03:05:11:00

you can see multiple versions of dropped table. Cool!

2. Check the content in a dropped table.

Dropped objects still occupy the space in the database until manually purged or automatically removed by the database itself.

SQL> select * from "BIN$NFlS+mOZHurgRAADung28g==$0"

3. Flashback dropped table from recycle bin

This gives you a chance to bring back the dropped table.

SQL> FLASHBACK TABLE SEQ_TELE TO BEFORE DROP RENAME TO SEQ_TELE_old;

--Flashback dropped table when it has mltiple versions
SQL> FLASHBACK TABLE "BIN$NFlS+mOZHurgRAADung28g==$0" TO BEFORE DROP RENAME TO TORCMPLIST_20070703051100;

4. Purge recycle bin

In order to avoid losing data by accident, by checking drop time of dropped objects, they can permanently and safely be removed from the database.

SQL> PURGE TABLE tablename;                   -- Remove specific table from recycle bin.
SQL> PURGE INDEX indexname;                   -- Remove specific index from recycle bin.
SQL> PURGE TABLESPACE ts_name;                -- Empty recycle bin for a specific tablespace under current user.
SQL> PURGE TABLESPACE ts_name USER username;  -- Empty recycle bin for a specific user under a specific tablespace.
SQL> PURGE RECYCLEBIN;                        -- Empty recycle bin for current user.
SQL> PURGE DBA_RECYCLEBIN;                    -- Empty the entire recycle bin in the database by DBA

5. Disable recycle bin feature

It is NOT recommneded as you lose the chance to easily restore the accidentally dropped table back. Think about how difficultly to do this prior to 10g.

SQL> alter seesion set recyclebin=off;
SQL> alter system set recyclebin=off;