Rewind Table Drop operation

Table Dropped by mistaken !!! offf

Before Oracle 10g release it is one of the biggest and time taken task for DBA's for recover DROPPED table through perform INCOMPLETE recovery.

But now in 10g it is very easy just taken few minutes and table is recovered without perform INCOMPLETE recovery or NOT restore and recovery backup.

It is called FLASHBACK drop table option

We need "DROP" & "SELECT" privilege on object where we want to perform FLASHBACK operation.

And RECYCLEBIN option must be enable otherwise is not going to recyclebin if it is disable.

In 10gr1 there is hidden parameter for enable recyclebin

SQL> alter database set "_recyclebin"=ON;

and later release we can just use recyclebin parameter

SQL> alter database set recyclebin=ON;

SQL> create table test ( no number);
Table created.
SQL> drop table test;
Table dropped.

SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------
TEST BIN$pqWN6pCzRxe+PtPucq/55A==$0 TABLE 2008-06-10:11:49:18

Now above table is dropped by mistake, but take it easy it is very easy with 10g to recover dropped table.

SQL> flashback table oem.test to before drop;
Flashback complete.
SQL> select count(*) from test;
COUNT(*)

----------

0

SQL> show recyclebin

For more details