Loading

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


1 Responses to “ORACLE SQL TUTORIAL: FLASHBACK QUERY”

Sridevi K said...
October 8, 2016 at 12:59 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.