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


Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backups using Shell Scripts

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback