FLASHBACK QUERY

FLASHBACK QUERY

It is used to retrieve the data which has been already committed without going for recovery.

Flashbacks are of two types

  • Time base flashback
  • SCN based flashback (SCN stands for System Change Number)

Ex:
1) Using time based flashback
a) SQL> Select *from student; — This will display all the rows
b) SQL> Delete student;
c) SQL> Commit; — this will commit the work.
d) SQL> Select *from student; — Here it will display nothing
e) Then execute the following procedures
SQL> Exec dbms_flashback.enable_at_time(sysdate-2/1440)
f) SQL> Select *from student;
— Here it will display the lost data
— The lost data will come but the current system time was used
g) SQL> Exec dbms_flashback.disable
— Here we have to disable the flashback to enable it again

2) Using SCN based flashback
a) Declare a variable to store SCN
SQL> Variable s number
b) Get the SCN
SQL> Exec :s := exec dbms_flashback.get_system_change_number
c) To see the SCN
SQL> Print s
d) Then execute the following procedures
SQL> Exec dbms_flashback.enable_at_system_change_number(:s)
SQL> Exec dbms_flashback.disable

Related posts:

Leave a Reply