SQL Query to recover dropped tables.

Is it possible to recover the dropped tables?

Yes, We can recover the dropped tables using flashback query if it is not permanently deleted.

Whenever you execute any dropped table query, dropped table information are stored in recyclebin. You can execute show recyclebin, it will display objects in recyclebin.

Example

SQL> CREATE TABLE TEST(ID INT);

Table created.

SQL> INSERT INTO TEST VALUES(1001);

1 row created.

SQL> INSERT INTO TEST VALUES(1002);

1 row created.

SQL> INSERT INTO TEST VALUES(1003);

1 row created.

SQL> INSERT INTO TEST VALUES(1004);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Q2h8H7dpTPa/+/wsarPBgA==$0 TABLE        2016-08-13:15:08:34

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;

Flashback complete.

SQL> SELECT * FROM TEST;

        ID
----------
      1001
      1002
      1003
      1004


We can also clear all the object from recyclebin using purge command.

Example

SQL> DROP TABLE TEST;

Table dropped.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$WztpWgD2TOCdtIxsAv+CSw==$0 TABLE        2016-08-13:15:14:53

SQL> PURGE RECYCLEBIN;

Recyclebin purged.


SQL> SHOW RECYCLEBIN

We can also recover dropped table with new name.

Example

SQL> DROP TABLE TEST;

Table dropped.

SQL> SELECT * FROM NEWTEST;
SELECT * FROM NEWTEST
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> FLASHBACK TABLE TEST TO BEFORE DROP
  2     RENAME TO NEWTEST;

Flashback complete.

SQL> SELECT * FROM NEWTEST;

        ID
----------
      1001
      1002
      1003

      1004