Recover a table from accidently drop using flash back

 There are different technique to recover a table from accidently dropped. We have to choose the best possible option based on situation and DB configuration. However recover a table from Recycle bin in always a preferable option.

Here are some example:

1. Recover table from Recycle Bin

2. Recover Table using Flashback Database feature

3. Recover table from Undo tablespace  using FlashBack  table .

4.Recover the table by using Database Point in time recovery.

5.Recover the table by clone the  source database to a auxiliary(Test) database and Export/Import the table to Source database.

6. Using Log Mining technology

However in  below we will discussed only Procedure   3.Recover table from Undo tablespace  using FlashBack  table

The FLASHBACK TABLE statement enables users to recover a table to a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application.

Restores all data in a specified table to a previous point in time described by a timestamp or SCN. An exclusive DML lock is held on a table while it is being  restored.

Automatically restores all of the table attributes, such as indexes, triggers, and  the likes that are necessary for an application to function with the flashed back  table.

Maintains any remote state in a distributed environment. For example, all of the  table modifications required by replication if a replicated table is flashed back.

Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK TABLE statement and another table that is not included in the FLASHBACK

TABLE statement.

Even after a flashback, the data in the original table is not lost. You can later revert to the original state.

Prerequisite to Use FlashBack Table feature:

1. Automatic undo management must be enable to  use the

flashback table feature. It is based on undo information stored in an

undo tablespace.

2. To use the FLASHBACK TABLE statement you must have been granted the

FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object

privilege on the table.

3.You must have SELECT, INSERT, DELETE, and

UPDATE privileges on the table.

4.The table that you are performing the flashback

operation on must have row movement enabled.

Performs the restore operation online:

Change the undo tablespace to retention gurantee immediately after you encountered the issue so

the unexpired segment are not  modified by any existing DML

SQL>alter tablespace UNDOTBS1 retention guarantee;

SQL>select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION

------------------------------ -----------

SYSTEM                         NOT APPLY

UNDOTBS1                       GUARANTEE

SYSAUX                         NOT APPLY

TEMP                           NOT APPLY

USERS                          NOT APPLY

7 rows selected.

SQL> ALTER TABLE Test_table enable row movement;

Table altered.

SQL> select * from Test_table;

     EMPNO EMPNAME

---------- ------------------------------

         1 Bhaskar

         2 Naveen

         3 Rajesh

         4 Suresh

SQL> select current_scn from v$database;

CURRENT_SCN

----------------

332348

SQL> connect scott/tiger

Connected.

SQL> insert into Test_table values(5,'DINESH');

1 row created.

SQL> insert into Test_table values(6,'JAI');

1 row created.

SQL> commit;

Commit complete.

SQL> connect / as sysdba

Connected.

SQL> select current_scn from v$database;

CURRENT_SCN

----------------

332376

SQL> connect scott/tiger

Connected.

SQL> select * from Test_table;

     EMPNO EMPNAME

---------- ------------------------------

         1 Bhaskar

         2 Naveen

         3 Rajesh

         4 Suresh

         5 Dinesh

         6 Jai

6 rows selected.

Now Flash back the table to old SCN or timestamp

SQL> flashback table Test_table to scn 332348;

Flashback complete.

SQL> select * from Test_table;

     EMPNO EMPNAME

---------- ------------------------------

         1 Bhaskar

         2 Naveen

         3 Rajesh

         4 Suresh

SQL> flashback table Test_table to scn 332376;

Flashback complete.

SQL> select * from Test_table;

     EMPNO EMPNAME

---------- ------------------------------

          1 Bhaskar

         2 Naveen

         3 Rajesh

         4 Suresh

         5 Dinesh

         6 Jai

6 rows selected.