RMAN
Oracle Flashback Drop
It takes only some seconds to restore the dropped table.
Starting from Oracle 10g, the recycle bin is used to keep dropped
objects until there is enough free space for new objects. When you
drop a table, it does not actually drop it, it just renames it and
moves it to the recycle bin.
- Scenario 4: Bob got a call from a user: Bob! I have
accidently dropped the most critical table of our project! Could
you restore it somehow without any downtime and data loss??
In this situation, Bob decides to restore the table using the
flashback dropfeature. To show
you how Bob has restored the table, perform the following steps.
- Create a table based on another table and query its row
count. Then drop it using drop table.
SQL>
create
table tbl_fl_drop as
2 select * from
dba_segments;
Table created.
SQL>
select
count(1)
from tbl_fl_drop;
COUNT(1)
----------
4133
SQL>
drop
table tbl_fl_drop;
Table dropped.
SQL>
select
count(1)
from
tbl_fl_drop;
select count(1) from tbl_fl_drop
*
ERROR at line 1:
ORA-00942: table or view does not exist
- As you see, the table is dropped. Now look to the recycle
bin:
SQL>
show
recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE
DROP TIME
---------------- ------------------------------ ------------
----------
TBL_FL_DROP BIN$fy2Ztc1m6bfgQAB/AQAUjw==$0 TABLE 2015-02-
9:11:42:15
The table is renamed BIN$fy2Ztc1m6bfgQAB/AQAUjw==$0 and moved
to the recycle bin.
- You can also query the dropped table using its recycle bin
name:
SQL>
select
count(1)
from
"bin$fy2ztc1m6bfgqab/aqaujw==$0";
COUNT(1)
----------
4133
- Now it is time to get the table back. Use the flashback
table to before drop command as follows:
SQL>
flashback
table tbl_fl_
drop
to before drop;
Flashback complete.
SQL>
select
count(*)
from
tbl_fl_drop;
COUNT(*)
----------
4133
SQL>
The table has been restored. Now by querying the recycle bin,
you find it empty:
SQL>
show
recyclebin;
SQL>
It is also possible to rename the table during the flashback
drop operation. If the table has dropped and another table was
created instead, then you need to rename the table when restoring
it from the recycle bin using the RENAME TO clause at the end of
the command. To test it, create a new table, drop it and create
another one with the same name:
SQL>
create
table tbl_fl_drop2 (id number);
Table created.
SQL>
drop
table tbl_fl_drop2;
Table dropped.
SQL> create
table tbl_fl_drop2 (id number);
Table created.
Now try to get the table back from the recycle bin without
renaming it. As you have created the table with the same name, you
will get an error:
SQL>
flashback
table tbl_fl_drop2 to before drop;
flashback table tbl_fl_drop2 to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
So you need to rename the table as follows:
SQL>
flashback
table tbl_fl_drop2 to before drop rename to tbl_fl_drop2_old;
Flashback complete.
SQL>
Retrieving Table From Recycle Bin
It is possible to restore the table that was dropped more than
one time. It is even possible to restore any version of that table
from recycle binif it is not
purged yet. When there are multiple tables with the same original
name in the recycle bin, the latest dropped one will be restored.
See the following example.
Create a table and delete it two times:
SQL>
create
table tbl_fl_drop (id number);
Table created.
SQL>
insert
into tbl_fl_drop values(1);
1 row created.
SQL>
commit;
Commit complete.
SQL>
drop
table tbl_fl_drop;
Table dropped.
SQL>
create
table tbl_fl_drop (id number);
Table created.
SQL>
insert
into tbl_fl_drop values(2);
1 row created.
SQL>
commit;
Commit complete.
SQL>
drop
table tbl_fl_drop;
Table dropped.
Query the recycle bin and try to restore the table using its
original name. The latest dropped one should be restored:
ORIGINAL NAME RECYCLEBIN
NAME OBJECT TYPE DROP TIME
------------ -------------------- ------
-------------------
TBL_FL_DROP BIN$gBQVXSYM0bXgQAB/AQAQ0w==$0 TABLE 2015-02-20:21:29:31
TBL_FL_DROP BIN$gBQVXSYL0bXgQAB/AQAQ0w==$0 TABLE 2015-02-20:21:29:17
SQL> flashback table tbl_fl_drop to before drop ;
Flashback complete.
ORIGINAL NAME RECYCLEBIN
NAME OBJECT TYPE DROP TIME
------------ -------------------- ------
-------------------
TBL_FL_DROP BIN$gBQVXSYL0bXgQAB/AQAQ0w==$0 TABLE 2015-02-20:21:29:17
SQL> select * from tbl_fl_drop;
ID
----------
2
If you want to restore what was dropped earlier, you
can use the recycle bin name of the table. But if there is already
a restored table with the same name, either drop it or use the
RENAME clause:
SQL>
flashback
table "bin$gbqvxsyl0bxgqab/aqaq0w==$0" to before drop;
flashback table "bin$gbqvxsyl0bxgqab/aqaq0w==$0" to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
SQL>
drop
table tbl_fl_drop;
table dropped.
SQL>
flashback
table "bin$gbqvxsyl0bxgqab/aqaq0w==$0" to before drop;
Flashback complete.
SQL>
select * from
tbl_fl_drop;
ID
----------
1
SQL>
Or use the RENAME TO clause instead of dropping the table:
SQL>
flashback
table "bin$gbqvxsyo0bxgqab/aqaq0w==$0" to before drop rename to
tbl_fl_dropped;
Flashback complete.
SQL>
Limitations of the Flashback Drop
There are also some limitations that should be taken into
account when using flashback drop.
When a table is dropped, all indexes are also dropped. And when
you flashback the table, indexes are also flashed back instead of
domain and bitmap join indexes. They are not saved in recycle bin.
However, if there is not enough space, you may not get the indexes
back, so you need to create the indexes again.
In case you there is not enough free space, Oracle first starts
to free the recycle bin. So you do not have a guarantee that all
tables you have dropped are now in the recycle bin. It may be that
they are purged automatically from the recycle bin due to the
space pressure. It is not possible to run any DML or DDL
statement against the objects that are in the recycle bin.
Note: You cannot flashback a truncated table
