Database Reference
In-Depth Information
The
FLASHBACK TABLE TO BEFORE DROP
operation only works if your database has the recycle bin feature enabled
(which it is by default). You can check the status of the recycle bin, as follows:
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- -------
recyclebin string on
FLASHBACK TABLE TO BEFORE DROP
When you drop a table, if you don't specify the
PURGE
clause, Oracle renames the table with a system-generated name.
Because the table isn't really dropped, you can use
FLASHBACK TABLE TO BEFORE DROP
to instruct Oracle to rename
the table with its original name. Here is an example. Suppose the
INV
table is accidentally dropped:
SQL> drop table inv;
Verify that the table has been renamed by viewing the contents of the recycle bin:
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -----------
INV BIN$BCRjF6KSbi/gU7fQTwrP+Q==$0 TABLE 2014-09-28:11:26:15
The
SHOW RECYCLEBIN
statement shows only tables that have been dropped. To get a more complete picture of
renamed objects, query the
RECYCLEBIN
view:
SQL> select object_name, original_name, type from recyclebin;
Here is the output:
OBJECT_NAME ORIGINAL_NAME TYPE
---------------------------------------- -------------------- ----------
BIN$BCRjF6KSbi/gU7fQTwrP+Q==$0 INV TABLE
BIN$BCRjF6KRbi/gU7fQTwrP+Q==$0 INV_TRIG TRIGGER
BIN$BCRjF6KQbi/gU7fQTwrP+Q==$0 INV_PK INDEX
In this output the table also has a primary key that was renamed when the object was dropped. To undrop the
table, do this:
SQL> flashback table inv to before drop;
The prior command restores the table to its original name. This statement, however, doesn't restore the index to
its original name:
SQL> select index_name from user_indexes where table_name='INV';
INDEX_NAME
---------------------------------
BIN$BCRjF6KQbi/gU7fQTwrP+Q==$0
