97. 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