Restore Dropped Tables with Flashback in SQL

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 flashback restore table use or recovered from recycle bin.

1. Check the table present in HR schema.

SQL> set line 999
SQL> set pages 999
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE

2. Drop the table as an example.

SQL> drop table test;

3. Check the table which dropped move to recycle bin.

Select * from dba_recyclebin;
Or
SELECT * FROM TAB;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$tyyNltv2QHy8L84OkHttFQ==$0 TABLE              

4. 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

5. Restore the table from recycle bin.

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;

6. Check the table restored back from recycle bin.

SQL> SELECT * FROM TAB;

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

Hopefully it will restore the table.

If you want to released the space from recycle bin or empty the recycle bin

-- Purge the recycle bin
PURGE RECYCLEBIN;
-- Drop table permanent without move to recycle bin
DROP TAB;