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