Restore table from dba recyclebin 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 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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.