• Arun Kumar

Flashback Table to Before Drop

In this article we will be looking at table flashback after it has been dropped by a user.


Scenario: Let us create a sample table for activity and drop the table. Next we will recover the table using flashback table via recycle bin.
Note: database recyclebin must be enabled for this activity else flashback table after drop will not work.

Create Sample Table


SQL> conn scott/tiger;
SQL> CREATE TABLE FLASH_EMP AS SELECT * FROM EMP;

Drop Above Table


SQL> DROP TABLE FLASH_EMP;
SQL> COMMIT;

Check Recyclebin


When recyclebin is enabled inside the database, by default all the dropped tables will reside in it. You can check the dropped tables inside recyclebin via below command


SQL> SHOW RECYCLEBIN;

You can even query the table inside recyclebin


SQL> select * from <recyclebin_table_name>;

Flashback Table Before Drop


There are three commands which you can use to flashback table to before drop.


Flashback table from the recyclebin to before drop. When a table is inside recyclebin, oracle will assign a new table name. You can use same name in below command under “” (double quotes) to flashback table


SQL> FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;

Flashback table with original table name to before drop


SQL> FLASHBACK TABLE SCOTT.FLASH_EMP TO BEFORE DROP;

Flashback table to before drop and rename it


SQL> FLASHBACK TABLE SCOTT.FLASH_EMP TO BEFORE DROP RENAME TO FLASH_NEW_EMP;


119 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir