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.5. Disable recycle bin feature
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
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;
