Monday, 23 February 2015


Oracle Flashback technology was introduced in Oracle 9i and added on to higher version. It provides a set of features using which we can view as well as recover data from Logical corruptions. Most of the Flashback technologies depend on the available UNDO data to retrieve older data.
Some of the Advantages of Flashback Technology are faster database point in time recovery and less performance overhead.Also we need to take care of the parameters that should be set for Flashback which are  given below:

·        DB_FLASHBACK_RETENTION_TARGET: Time limit for the deleted data to be retained

·        DB_RECOVERY_FILE_DEST_SIZE: Size limit for the maximum data that can be retained.
Alter System Set DB_RECOVERY_FILE_DEST_SIZE=536870912

·        DB_RECOVERY_FILE_DEST: Location where the data needs to be retained
Alter System Set DB_RECOVERY_FILE_DEST='/Source/File/’

Flashback technology gives six different ways to fix logical corruption.

a) Oracle Flashback Query:
The Flashback query feature is used to views set of row at a specified point of time in the past. This feature uses the UNDO data (stored based on the undo retention parameter) to view the historical data and reconstruct the data if it was lost accidentally.
Useful when you accidentally delete number of rows and commit the changes.

Select .. As Of SCN | Timestamp

AS OF TIMESTAMP (Mention The Timestamp Here)

SELECT * FROM Ftest  As Of SCN (Mention The SCN Here)

b) Oracle Flashback Version Query 
This features helps to view all the versions of all the rows that ever existted in one or more tables in between two points in time or system change numbers (SCN).This feature also depends on UNDO data.
Select .. Versions Between SCN | Timestamp And SCN | Timestamp
 Select Version_Xid,Dept_Id
From Dept
Version Between SCN (Referencing A Start And End SCN)

Select Version_Xid,Dept_Id
From Dept
Version Between TIMESTAMP (Referencing A Start And End Timestamp)

) Oracle Flashback Drop 
Flashback drop is used to restore tables and depended objects accidentally dropped. After restoring the table will be renamed as its original whereas the indexes will have system generated names.


Before doing flashback confirm that the dropped object has not been purged

Select OBJECT_NAME,ORIGINAL_NAME From User_Recyclebin;

d) Oracle Flashback Database 
This feature is used to recover the database from logical corruptions to specified point in time/scn. To flashback the database you must have the SYSDBA privilege.

Flashback Database To SCN  | Restore Point



We can also create a restore point and restore the database to the restore point.

e) Oracle Flashback Transaction Query
 Flashback Transaction query allows viewing changes made by single transaction or all transactions during a period of time .This feature is used during perform analysis, audit transactions to check data at transaction level.

Transaction Query Features uses the FLASHBACK_TRANSACTION_QUERY view for retrieving transaction information.

SELECT Logon_User, Operation, Start_Timestamp, Undo_Sql
FROM Flashback_Transaction_Query
WHERE Xid In (
SELECT  Versions_Xid
FROM  Emp BETWEEN TIMESTAMP (Systimestamp - Interval '6' Minute) AND Systimestamp);

f) Oracle Flashback Table
Flashback Table feature helps to recover table or set of tables to particular timestamp or SCN to reverse unwanted updates. 
To recover the table we need to set the below things.
  • Requires undo data.
  • Row movement must be enabled.
  • Must have SELECT, INSERT, DELETE, and ALTER privileges on the table
Flashback Table .. To Scn | Timestamp
Flashback Table Dept To Timestamp (Timestamp Value)



  1. Awesome this is interesting.. Great Wonderful information.. I am impressing Thanks..

  2. Get Quick Recovery of Data after Accidental Delete with Cassandra Technical Support
    Rapidly recoup everything erased information or data of Cassandra Database with Cognegic's Cassandra Database Support or Cassandra Database Consulting and Support. Our specialists have energy and ability to recuperate your everything information identified with Cassandra and separated from that we likewise recoup and give reinforcement to whole database including MongoDB, MySQL, Oracle, MS SQL server et cetera. You can whenever contact to our Apache Cassandra Support and get the propel support.
    For More Info:
    Contact Number: 1-800-450-8670
    Email Address-
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

  3. Hi. I would like to know who is your favorite microsoft partner in uk. And have you heard about Anegis? Could you recommend me this company? What do you think about them?

  4. Seen the whole article content. There are a little bit of really informative info here. many thanks. “Brilliance is typically the act of an individual, but incredible stupidity can usually be traced to an organization.” by Jon Bentley.. AMREP Technology Center

  5. Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
    internships provider in hyderabad
    internship in hyderabad for cse


Related Posts Plugin for WordPress, Blogger...