How to restore a dropped table using Flashback Drop

Flashback Drop is a feature in Oracle Database through which we can restore table which has been accidentally dropped.

To use this feature FLASHBACK has to be enabled in your database . To enable FlASHBACK
visit Enable Flashback in Oracle

Database has to be in Archive log mode. To enable Archive log mode in Database visit Change Archive mode of Database

This feature uses recycle bin to restore dropped table. We can specify either the name of the table in recycle bin or the original table name to restore. We can also rename the table while restoring it from the recycle bin using "RENAME TO" clause.

PFB example to restore a table and its dependent objects through Flashback Drop :-

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                    TABLE                             VALID
AMIT_PP                                              PROCEDURE                 VALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_TT                                             TRIGGER                         VALID
AMIT_VV   VIEW                               VALID

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME         STATUS
-------------                 --------
D_UI                        VALID
ID_NI                       VALID
D_NI                        VALID
ORDER_NI              VALID
USERID                  VALID
ID_TIME                VALID
H_NI                       VALID
DEACT_H_NI        VALID

8 rows selected.

SQL> drop table AMIT cascade constraints;

Table dropped.

########### if we use purge in Drop command then we cannot restore it from recycle bin##########

SQL> drop table AMIT cascade constraints purge;

Table dropped.

Query the user_recyclebin to get the details of dropped table and its dependents. Recyclebin is the synonym for user_recyclebin. The system generated name of the dropped object is unique across database. Dropping a table drop the associated indexes , triggers but not Procedures , Functions and views . 

When we restore a table from Recycle bin , the dependent objects such as triggers , indexes etc do not get their original names back, they have their system generated names with them . We have to manually rename them after the table restore operation. So before restoring the table from Recycle bin, make a note of the system generated recycle bin names of the dependent objects. 

After dropping AMIT table , before restoring it run below query :-

SQL> select  object_name, original_name,type, createtime FROM recyclebin;

OBJECT_NAME                                                             ORIGINAL_NAME                    TYPE                      CREATETIME
-----------------------------------------------------------------          --------------------------------   ------------------------- -------------------
BIN$8RsifQMpTIe2DYcWdrZBsQ==$0                         D_UI                      INDEX   2013-07-01:11:03:38
BIN$zwr7JQ55SOecmnkTOAbGkA==$0                       ID_NI                     INDEX   2013-07-01:11:03:38
BIN$YagDo75NS0enszVEvCtv+g==$0                           D_NI                      INDEX   2013-07-01:11:03:38
BIN$amdEjo63Q5GfqR2aqyLrYg==$0                           ORDER_NI            INDEX   2013-07-01:11:03:38
BIN$kow3P8xOSwC1fzhLep5wgg==$0                          USERID                 INDEX   2013-07-01:11:03:38
BIN$8epaA+Z7RyeIQ+zTiT8r4g==$0                              ID_TIME               INDEX   2013-07-01:11:03:39
BIN$MPqYce8sQLGUoxTor3zMVw==$0                        H_NI                      INDEX   2013-07-01:11:03:39
BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0                       DEACT_H_NI        INDEX   2013-07-01:11:03:39
BIN$kzeXKoZoSwqycBiH6X8FzA==$0                          AMIT_TT              TRIGGER   2013-07-09:12:48:03
BIN$C5EACqXLRZyACHAhLjC/LQ==$0                       AMIT                     TABLE   2013-07-01:11:02:10

Now restore table through Flashback drop :-

SQL> flashback table AMIT to before drop;

Flashback complete.

############ if you want to rename AMIT to TEST then use RENAME ################

SQL> flashback table AMIT to before drop rename to TEST;

Flashback complete

Once the table has been restored , dependent objects also have been restored. Dependent objects like indexes , triggers have their system generated names with them and are in Valid state. Dependent objects like procedures , Functions, View associated with table are in invalid state . We have to compile them manually to make them valid.

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME                                                             STATUS
------------------------------------------------------                  --------
BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0                  VALID
BIN$MPqYce8sQLGUoxTor3zMVw==$0                  VALID
BIN$8epaA+Z7RyeIQ+zTiT8r4g==$0                       VALID
BIN$kow3P8xOSwC1fzhLep5wgg==$0                    VALID
BIN$amdEjo63Q5GfqR2aqyLrYg==$0                     VALID
BIN$YagDo75NS0enszVEvCtv+g==$0                    VALID
BIN$zwr7JQ55SOecmnkTOAbGkA==$0                 VALID
BIN$8RsifQMpTIe2DYcWdrZBsQ==$0                    VALID

8 rows selected.

SQL> select TRIGGER_NAME,status from user_triggers where TABLE_NAME='AMIT';

TRIGGER_NAME                                          STATUS
------------------------------                                  --------
BIN$kzeXKoZoSwqycBiH6X8FzA==$0        ENABLED

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                      TABLE                             VALID
AMIT_PP                                              PROCEDURE                 INVALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_VV    VIEW                               INVALID

We have to manully rename Indexes and triggers according to the note we have made before restoring.

SQL> alter trigger "BIN$kzeXKoZoSwqycBiH6X8FzA==$0" rename to AMIT_TT;

Trigger altered.

SQL> alter index "BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0" rename to ID_NI;

Index altered.

SQL> alter index "BIN$MPqYce8sQLGUoxTor3zMVw==$0" rename to D_UI;

Like this rename all the indexes . Now compile the procedures and Views.

SQL> alter view AMIT_VV compile;

View altered.

SQL> alter procedure AMIT_PP compile;

Procedure altered.

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME         STATUS
-------------                 --------
D_UI                        VALID
ID_NI                       VALID
D_NI                        VALID
ORDER_NI              VALID
USERID                  VALID
ID_TIME                VALID
H_NI                       VALID
DEACT_H_NI        VALID

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                      TABLE                             VALID
AMIT_PP                                              PROCEDURE                 VALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_TT                                             TRIGGER                         VALID
AMIT_VV    VIEW                               VALID

Restoration of table from Recylebin completed.

I hope this article helped you.

Regards,
Amit Rath