Flashback Table to SCN

In this article we will look at table flashback to a particular SCN number. Let’s take a scenario where you delete one record from a table and want to flashback table to SCN before record delete.

Check Current SCN

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

Delete One Record

SQL> conn scott/tiger
SQL> SELECT * FROM EMP;
SQL> DELETE FROM EMP WHERE EMPNO=7934;
SQL> commit;

Flashback Table to SCN

To flashback table to SCN before record delete, we must enable row movement on the table as sys user

SQL> conn / as sysdba
SQL> SELECT table_name, row_movement FROM dba_tables where table_name='EMP';
SQL> Alter table scott.emp enable row movement;

Flashback the table to before drop SCN number.


SQL> FLASHBACK TABLE EMP TO SCN <scn_no>;
SQL> SELECT * FROM SCOTT.EMP;

Was this article helpful?

Related Articles

Leave a Comment