Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL Flashback

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
Share this article :

0 comments:

Post a Comment