Flashback restore table from drop command in Oracle

Flashback query only help to restore the data from the table. It cannot restore from DDL Operations.

In following example we use HR schema having table TEST which we drop and recover from flash back restore table use or recovered from recycle bin.

Check the table present in HR schema

SQL> set line 999
SQL> set pages 999
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE

Drop the table as an example.

SQL> drop table test;


Check the table which dropped move to recycle bin

SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$tyyNltv2QHy8L84OkHttFQ==$0 TABLE


Check the object present in recycle bin

SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$tyyNltv2QHy8L84OkHttFQ==$0 TABLE 2018-09-06:15:04:41


Restore the table from recycle bin

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;


Check the table restored back from recycle bin.

SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
--------- ------- ----------
TEST TABLE


For free the utilized space you need to purge the table from recycle bin or drop table permanent:

-- Purge the recycle bin
PURGE RECYCLEBIN;

-- Drop table permanent without move to recycle bin
DROP TABLE EMPLOYEE PURGE;