Flashback Table to Before drop

Oracle FlashBack Table

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline.

Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints

Example:

 sql> DROP TABLE flashback_drop_test;

 sql> SHOW RECYCLEBIN

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE 2004-03-29:11:09:07

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name.

sql> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

You can retrieve it and assign it a new name using a RENAME TO clause.

sql> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP

RENAME TO flashback_drop_test_old;

### Several purge options exist:

PURGE TABLE tablename;

PURGE INDEX indexname;

PURGE TABLESPACE ts_name;

PURGE TABLESPACE ts_name USER username;

PURGE RECYCLEBIN;

PURGE DBA_RECYCLEBIN;   

### Query Recycle Bin

col original_name format a10

col owner format a10

col type format a10

col droptime format a10

col pace format a12

select owner, original_name, object_name, type, droptime

from dba_recyclebin

where can_undrop='YES';

### Query the dropped table data from Recycle bin

SELECT * FROM "BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";

### Bypass the Recycle Bin

drop table flashback_drop_test purge;

Hope this help. Regards Rupam