Flashback restore table from drop command in Oracle

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;

Advertisements

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.