How do I recover an Oracle database table with recyclebin?
One feature of Oracle 10g is recyclebin, which allows you to recover dropped tables. With recyclebin, any tables you drop do not actually get deleted. Instead, Oracle renames the table and its associated objects to a system-generated name that begins with BIN$.
For example, consider this simple table:
SQL> create table testing (col varchar2(10), row_chng_dt date);
Table created.
SQL> insert into testing values ('Version1', sysdate);
1 row created.
SQL> select * from testing ;
COL ROW_CHNG
---------- --------
Version1 16:10:03
Dropping this table will place it in recyclebin:
SQL> drop table testing;
Table dropped.
SQL> select object_name, original_name, type, droptime from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME
------------------------------ ------------- ----- ---------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TESTING TABLE 2006-09-01:16:10:12
Dropping this table caused it to be renamed. The table data is still there, and you can query it as you would a normal table:
SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;
COL ROW_CHNG
---------- --------
Version1 16:10:03
Because the table data is still there, it's very easy to "undrop" the table; use the FLASHBACK TABLE... TO BEFORE DROP command. This simply renames the BIN$... table to its original name:
SQL> flashback table testing to before drop;
Flashback complete.
SQL> select * from testing ;
COL ROW_CHNG
---------- --------
Version1 16:10:03
SQL> select * from recyclebin ;
no rows selected
Note: After a table has been dropped, it has only been renamed. It is still part of your tablespace, and this space counts against your user tablespace quota. The space will not be reclaimed until you restore the table with the flashback command, or use purge to clear the table out of recyclebin:
SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;
Table purged.
Oracle will leave objects in recyclebin until the tablespace runs out of space, or until you reach your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation.
