Friday, October 28, 2011
ORACLE SQL TUTORIAL: FLASHBACK QUERY
Used to retrieve the data which has been already committed with
out 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
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “ORACLE SQL TUTORIAL: FLASHBACK QUERY”
Post a Comment