Oracle Flashback query feature enables you to rewind the data of one or more tables prior to time with some limitations in length.
This feature enables you to recover from logical data corruption such as the accidental addition or deletion of rows.
Keypoint - You must enable row movement for the table before the flashback. Otherwise, this may cause some integrity of the application if it is using ROWIDs of the tables.
We can use the below options to go back in time
1. SCN
2. TIMESTAMP
Here I am demonstrating the Flashback Table using SCN.
I have an EMP table on the SCOTT schema that has 21 rows.
SQL> select count(*) from emp;
COUNT(*)
----------
21
Get the CURRENT_SCN before dropping the database
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3369153
Delete rows
SQL> delete from emp;
21 rows deleted.
SQL> select count(*) from emp;
COUNT(*)
----------
0
SQL> commit;
Commit complete.
Check the data in EMP as of SCN you have above.
SQL> select count(*) from emp as of scn 3369153;
COUNT(*)
----------
21
Enable row movement for table EMP
SQL> alter table emp enable row movement;
Table altered.
Flashback table to the previous Point-in-Time
SQL> flashback table emp to scn 3369153;
Flashback complete.
SQL> select count(*) from emp;
COUNT(*)
----------
21
Comments