top of page
Search

Oracle Flashback Table Demo using SCN

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


26 views0 comments

Recent Posts

See All

Kommentare


Contact Me

Tel: 7989359581

Lakshminarayana0071@gmail.com

  • Facebook Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon

Thanks for submitting!

© 2023 by Phil Steer . Proudly created with Wix.com

bottom of page