Recovering Accidentally Lost Data Using Oracle's Flashback Query

Friday May 9th 2003 by Staff

Recent studies show that nearly 50% of system outages are due to human errors. Oracle 9i's new feature, Flashback Query allows the user to view an old image of data that has been modified and committed by the DML statement, without requiring any structural changes to the database.

by Sreeram Surapaneni


Recent studies show that nearly 50% of system outages are due to human errors. The ability to recover from these user errors using a simple, easy to use interface is challenging. Most database products today do not offer any solutions, thus administrators have no option but to recover the database using a previous backup. This not only requires the database to be unavailable during the period of recovery but also leads loss of valid transactions if the database to be restored to a point in time in the past. Oracle 9i's new feature, Flashback Query allows the users to view the old image of data that was modified and committed by DML statement, without requiring any structural changes to the database.


Oracle database uses a version based read consistency mechanism by saving an image of the data prior to making any modifications. These changes are stored in the undo tablespace. If the users decide not to commit the transaction, Oracle uses the data saved in the undo tablespace to recreate the original data. The Flashback Query uses the same mechanism to construct an image of the data, as it existed at a time in the past. Oracle professionals can specify how long they wish to retain the undo data using the UNDO_RETENTION parameter in the init.ora file and thereby can control how far back a Flashback Query can go.

The Flashback query feature in an application can be utilized by using either of the following two techniques. Using the AS OF clause in SQL queries to specify a past time is the simplest way to implement the Flashback query. This method allows you to perform DDL operations such as creating and truncating tables, and DML operations such as inserts and deletes, in the same session as queries using the AS OF clause. Alternatively, it can be implemented using the DBMS_FLASHBACK package but we must open a cursor before making a call to disable the Flashback mode. We can fetch results from past data from the cursor, then issue INSERT or UPDATE statements against the current state of the Database. This is illustrated in the next section with examples.

Before ordinary users can take advantage of this functionality, the Database Administrators must perform the following actions.

  • Configure for Automatic undo management: set initialization parameter undo_management=auto and undo_tablespace= <undo tablespace name> in the init.ora file.
  • Specify undo retention time by setting undo_reention = <time in seconds> in the init.ora.

    For eg:

  • Grant execute privilege on dbms_flashback package to the users that need to perform Flashback Query.
  • Grant 'FLASHBACK' privilege on the table's that users need to perform flashback query. Alternatively, 'FLASHBACK ANY TABLE' system privilege to allow the user to use this feature on any table except data dictionary tables.

Please note that the flashback query is turned off when the session ends, and if there is no explicit call to the disable procedure.


  • Flashback Query is supported by the export utility to export data which is consistent as of a specified point in time.
  • PL/SQL cursors opened in Flashback mode area available for DML after Flashback mode is disabled.
by Sreeram Surapaneni


Having discussed the concepts behind the Flashback Query, let us now examine how it can be used in a real world environment. Let us take the example of an email-company where the set of email addresses was accidently deleted by the DBA from users table at 9:05AM. This mistake was later discovered at 11:30AM. If the company decides to rollback the database just prior to 9:05 AM, when the error occurred, in order to "recover" the deleted data, it would not only loose all subsequent transactions but also the system will be unavailable for the duration of the recovery. Clearly, this is not a solution for the service- company since it forces the company to compromise the system availability to serve the existing users in the system. Flashback query makes it possible for the company to correct the mistake without effecting normal operations as illustrated below.

Example 1
Using "AS OF" clause:

	TO_TIMESTAMP ('22-APR-03 9:04:58','DD-MON-YY HH24: MI: SS')


Using "DBMS_FLASHBACK utility":

    SELECT *
    FROM   users;
v_rec  c%ROWTYPE;
  OPEN c;
    FETCH c INTO v_row; 
    (v_rec.user_id, v_rec.first_name, 
     v_rec.phone_number, v_rec.address,
  CLOSE c;
by Sreeram Surapaneni

Example 2 Using "AS OF " clause with SCN:

A precise method of specifying the flashback point is using the SCN directly, instead of specifying a timestamp as in the above example, but it requires that we store the SCN in advance. The current system change number can be returned using the GET_SYSTEM_CHANGE_NUMBER function of the DBMS_FLASHBACK package.

Let us take another example where an application developer needs to build new functionality using the past versions of user data and application level, end user service-level error correction capabilities. In the example below, records in the users table, where users belong to state 'CA' and 'NY,' were deleted by the application interface [ See procedure line DELETE FROM users WHERE state in ('CA,'NY');]. We can use the data that existed immediately before the delete by inserting it into the users table, [see OPEN c FOR 'SELECT * FROM users AS OF SCN :1 WHERE state in ('CA','NY'); below.]

  TYPE user_cur IS REF CURSOR;
  c  user_cur;
  cvar  users%ROWTYPE;
  old_scn NUMBER;
  old_scn := dbms_flashback.get_system_change_number;
  DELETE FROM users WHERE state in ('CA,'NY');
/* User choose to delete California and New York users from users table */
/* Find that earlier delete was in error. Need to use the data it existed 
	immediately before delete. */
  OPEN c FOR 'SELECT * FROM users AS OF SCN :1 WHERE state in ('CA','NY');
    USING old_scn;
    FETCH c INTO cvar;
    dbms_output.put_line ('Recovering  'CA', and 'NY' users: ' || cvar.last_name);
    INSERT INTO users VALUES cvar;

NOTE: It is important we note that SYSDATE and DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER functions always return the current SYSDATE and SCN respectively, regardless of whether you are in flashback query mode or not.

Example 3

To find all users that were added today to the users table, or to find the users whose data was changed today so their data doesn't match the data from yesterday is illustrated with following query.

CREATE TABLE users_changed_today AS
 	SELECT * FROM users


  • Some DDLs that alter the structure of a table, such as drop column, invalidate the redo information.. It is not possible to retrieve a snapshot of data from a point earlier than the time such DDLs were executed. Trying such a query results in an ORA-1466 error.

  • Oracle tracks the SCNs at five-minute intervals and logs the information for the

    last 5 days of operation. When we enable the Flashback, this log information is used to determine the SCN associated with the time you specify and it is the basis for any flashback queries. Therefore, we are never able to flashback beyond five days and specifying a time will only find Flashback copy to the nearest five-minute interval.

  • LOB columns might require significant amounts of storage to keep undo data. Therefore, you must define in advance which LOB columns you want to keep the undo information in, in order to use the undo data for a flashback query.

  • Flashback Query does apply to code objects (Packages, Procedures, Function or Triggers). If invoked, the current definition will be executed against the flashback.


The Flashback Query feature enables recovery of an entirely new class of errors--those committed by users, with little or no intervention from dba. It provides an online mechanism to recover from such errors, thereby enhancing system availability and business profitability. However, the Flashback query mode can be entered only at the beginning of a transaction. If you have issued DML statements to change your data, then you must COMMIT before you can flash back. In addition, you will not be able to flash back to a point in time prior to the most recent change in a table's structure. That is because flashback queries use current data dictionary data. Even a simple change to a column datatype limits your ability to flash back.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved