Recover Objects from Oracle Recyclebin 11g/10g

Using my previous example

SQL> select object_name, original_name, type, can_undrop, can_purge, droptime
from recyclebin;

OBJECT_NAME    ORIGINAL_NAME    TYPE    UND    PUR    DROPTIME
————-    ————–    —-    —-    —    ———
BIN$GI74VOhyTp29f91G/w1VOg==$0    T1    TABLE    YES    YES    2011-02-08:04:23:01
BIN$LPbnFGYmST+6vTnvrdb9WQ==$0    T1    TABLE    YES    YES    2011-02-08:04:22:26

SQL> flashback table t1 to before drop;

Flashback complete.

SQL> select object_name, original_name, type, can_undrop, can_purge, droptime
from recyclebin;

OBJECT_NAME    ORIGINAL_NAME    TYPE    CAN_UNDROP    CAN_PURGE    DROPTIME
————–    ————-    ——    ———-    ———-    ———-
BIN$LPbnFGYmST+6vTnvrdb9WQ==$0    T1    TABLE    YES    YES    2011-02-08:04:22:26

Oracle will always restore the last version of the dropped table. But if we want to restore the earlier version of the table, we can simply refer to the correct version of the table by using the correct BIN$ name of the object

SQL> flashback table “BIN$LPbnFGYmST+6vTnvrdb9WQ==$0” to before drop;

Flashback complete.

Oracle will leave objects in recyclebin until the tablespace runs out of space, or until users reach quota on the tablespace. At that point, Oracle purges the objects one at a time in chronological order, until there is enough space for the current operation.

Leave a Comment