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