10 Developing Flashback Applications

Application Development Features

In application development, you can use flashback features to report on historical data or undo erroneous changes. Flashback features include the following:

• Oracle Flashback Query

  You can use this feature to retrieve data for a time in the past that you specify using the AS OF clause of the SELECT statement.

• Oracle Flashback Version Query

  You can use this feature to retrieve metadata and historical data for a specific time interval. For example, you can view all the rows of a table that ever existed during a given time interval. Metadata about the different versions of rows includes start and end time, type of change operation, and identity of the transaction that created the row version. You use the VERSIONS BETWEEN clause of the SELECT statement to create a Flashback Version Query.

• Oracle Flashback Transaction Query

  You can use this feature to retrieve metadata and historical data for a given transaction or for all transactions within a given time interval. You can also obtain the SQL code to undo the changes to particular rows affected by a transaction. Typically, you use Flashback Transaction Query in conjunction with a Flashback Version Query that provides the transaction IDs for the rows of interest. To perform a Flashback Transaction Query, select from the FLASHBACK_TRANSACTION_QUERY view.

• DBMS_FLASHBACK package

  You can use this feature to set the internal Oracle clock to a time in the past so that you can examine data current at that time.

Database Administration Features

You can use the following features for application development or interactively as a database user or administrator:

• DBMS_FLASHBACK package

• Flashback Query

• Flashback Version Query

• Flashback Transaction Query

Typically, you use the following flashback features only in database administration:

• Oracle Flashback Table

  You can use this feature to recover a table to its state at a previous point in time. You can restore table data while the database is on line, undoing changes to only the specified table.

• Oracle Flashback Drop

  You can use this feature to recover a dropped table. This reverses the effects of a DROP TABLE statement.

• Oracle Flashback Database

  You can use this feature to quickly return the database to an earlier point in time, by undoing all of the changes that have taken place since then. This is fast, because you do not have to restore database backups.

Flashback Database, Flashback Table, and Flashback Drop are primarily data recovery mechanisms and are therefore documented elsewhere. The other flashback features, while valuable in data recovery scenarios, are useful for application development. They are the focus of this chapter.

Examining Past Data: Example

This example uses a Flashback Query to examine the state of a table at a previous time. Suppose that a DBA discovers at 12:30 PM that the row for employee Chung had been deleted from the employees table. The DBA also knows that at 9:30AM the data for Chung was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30 to find out what data had been lost. If appropriate, the DBA can then re-insert the lost data.

Example 10-1 retrieves the state of the record for Chung at 9:30AM, April 4, 2004:

SELECT * FROM employees AS OF TIMESTAMP 
   TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE last_name = 'Chung';

The update in Example 10-2 restores Chung's information to the employees table:

INSERT INTO employees 
    (SELECT * FROM employees AS OF TIMESTAMP 
     TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
     WHERE last_name = 'Chung');

Tips for Using Flashback Query

Keep the following in mind when using a Flashback Query (SELECT ... AS OF):

• You can specify or omit the AS OF clause for each table and specify different times for different tables. Use an AS OF clause in a query to perform DDL operations (such as creating and truncating tables) or DML operations (such as inserting and deleting) in the same session as the query.

• To use the results of a Flashback Query in a DDL or DML statement that affects the current state of the database, use an AS OF clause inside an INSERT or CREATE TABLE AS SELECT statement.

• When choosing whether to use a timestamp or an SCN in Flashback Query, remember that Oracle Database uses SCNs internally and maps these to timestamps at a granularity of 3 seconds. If a possible 3-second error (maximum) is important to a Flashback Query in your application, then use an SCN instead of a timestamp. Refer to "Flashback Tips – General".

• You can create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view. If you specify a relative time by subtracting from the current time on the database host, then the past time is recalculated for each query. For example:

  CREATE VIEW hour_ago AS
    SELECT * FROM employees AS OF
      TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
  -- SYSTIMESTAMP refers to the time zone of the database host environment
  
  

• You can use the AS OF clause in self-joins, or in set operations such as INTERSECT and MINUS, to extract or compare data from two different times. You can store the results by preceding a Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement. For example, the following query reinserts into table employees the rows that existed an hour ago:

  INSERT INTO employees 
    (SELECT * FROM employees AS OF 
       TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE))
  -- SYSTIMESTAMP refers to the time zone of the database host environment
    MINUS SELECT * FROM employees);
  

Flashback Transaction Query and Flashback Version Query: Example

This example demonstrates the use of a Flashback Transaction Query in conjunction with a Flashback Version Query. The example assumes simple variations of the employees and departments tables in the sample hr schema.

In this example, a DBA carries out the following series of actions in SQL*Plus:

connect hr/hr
CREATE TABLE emp 
   (empno   NUMBER PRIMARY KEY, 
    empname VARCHAR2(16), 
    salary  NUMBER);
INSERT INTO emp VALUES (111, 'Mike', 555);
COMMIT;

CREATE TABLE dept 
   (deptno   NUMBER, 
    deptname VARCHAR2(32));
INSERT INTO dept VALUES (10, 'Accounting');
COMMIT;

At this point, emp and dept have one row each. In terms of row versions, each table has one version of one row. Next, suppose that an erroneous transaction deletes employee id 111 from table emp:

UPDATE emp SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept VALUES (20, 'Finance'); 
DELETE FROM emp WHERE empno = 111;
COMMIT;

Subsequently, a new transaction reinserts employee id 111 with a new employee name into the emp table.

INSERT INTO emp VALUES (111, 'Tom', 777);
UPDATE emp SET salary = salary + 100 WHERE empno = 111;
UPDATE emp SET salary = salary + 50 WHERE empno = 111;
COMMIT;

At this point, the DBA detects the application error and needs to diagnose the problem. The DBA issues the following query to retrieve versions of the rows in the emp table that correspond to empno 111. The query uses Flashback Version Query pseudocolumns.

connect dba_name/password
SELECT versions_xid XID, versions_startscn START_SCN,
  versions_endscn END_SCN, versions_operation OPERATION,
  empname, salary FROM hr.emp
  VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
  where empno = 111;

XID              START_SCN  END_SCN   OPERATION  EMPNAME    SALARY
---------------- ---------- --------- ---------- ---------- ----------
0004000700000058 113855               I          Tom        927
000200030000002D 113564               D          Mike       555
000200030000002E 112670     113564    I          Mike       555
3 rows selected

The results table reads chronologically, from bottom to top. The third row corresponds to the version of the row in emp that was originally inserted in the table when the table was created. The second row corresponds to the row in emp that was deleted by the erroneous transaction. The first row corresponds to the version of the row in emp that was reinserted with a new employee name.

The DBA identifies transaction 000200030000002D as the erroneous transaction and issues the following Flashback Transaction Query to audit all changes made by this transaction:

SELECT  xid, start_scn START, commit_scn COMMIT, 
        operation OP, logon_user USER, 
        undo_sql FROM flashback_transaction_query
        WHERE xid = HEXTORAW('000200030000002D');

XID               START   COMMIT  OP       USER   UNDO_SQL
----------------  -----   ------  --       ----   ---------------------------
000200030000002D  195243  195244  DELETE   HR     insert into "HR"."EMP" 
("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');

000200030000002D  195243  195244  INSERT   HR     delete from "HR"."DEPT" 
where ROWID = 'AAAKD4AABAAAJ3BAAB';

000200030000002D  195243  195244  UPDATE   HR     update "HR"."EMP" 
set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';

000200030000002D  195243  113565  BEGIN  HR

4 rows selected

The rightmost column (undo_sql) contains the SQL code that will undo the corresponding change operation. The DBA can execute this code to undo the changes made by that transaction. The USER column (logon_user) shows the user responsible for the transaction.

A DBA might also be interested in knowing all changes made in a certain time window. In our scenario, the DBA performs the following query to view the details of all transactions that executed since the erroneous transaction identified earlier (including the erroneous transaction itself):

SELECT xid, start_scn, commit_scn, operation, table_name, table_owner
  FROM flashback_transaction_query
  WHERE table_owner = 'HR' AND
        start_timestamp >=
          TO_TIMESTAMP ('2002-04-16 11:00:00','YYYY-MM-DD HH:MI:SS');

XID               START_SCN  COMMIT_SCN  OPERATION  TABLE_NAME  TABLE_OWNER
----------------  ---------  ----------  ---------  ----------  -----------
0004000700000058  195245     195246      UPDATE     EMP         HR
0004000700000058  195245     195246      UPDATE     EMP         HR
0004000700000058  195245     195246      INSERT     EMP         HR
000200030000002D  195243     195244      DELETE     EMP         HR
000200030000002D  195243     195244      INSERT     DEPT        HR
000200030000002D  195243     195244      UPDATE     EMP         HR

6 rows selected

Flashback Tips – Performance

• For better performance, generate statistics on all tables involved in a Flashback Query by using the DBMS_STATS package, and keep the statistics current. Flashback Query always uses the cost-based optimizer, which relies on these statistics.

• The performance of a query into the past depends on how much undo data must be accessed. For better performance, use queries to select small sets of past data using indexes, not to scan entire tables. If you must do a full table scan, consider adding a parallel hint to the query.

• The performance cost in I/O is the cost of paging in data and undo blocks that are not already in the buffer cache. The performance cost in CPU use is the cost of applying undo information to affected data blocks. When operating on changes in the recent past, flashback features essentially CPU bound.

• Use index structures for Flashback Version Query: the database keeps undo data for index changes as well as data changes. Performance of index lookup-based Flashback Version Query is an order of magnitude faster than the full table scans that are otherwise needed.

• In a Flashback Transaction Query, the type of the xid column is RAW(8). To take advantage of the index built on the xid column, use the HEXTORAW conversion function: HEXTORAW(xid).

• Flashback Query against a materialized view does not take advantage of query rewrite optimizations.

  See Also:

  Oracle Database Performance Tuning Guide

Flashback Tips – General

• Use the DBMS_FLASHBACK package or other flashback features? Use ENABLE/DISABLE calls to the DBMS_FLASHBACK package around SQL code that you do not control, or when you want to use the same past time for several consecutive queries. Use Flashback Query, Flashback Version Query, or Flashback Transaction Query for SQL that you write, for convenience. A Flashback Query, for example, is flexible enough to do comparisons and store results in a single query.

• To obtain an SCN to use later with a flashback feature, use DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER.

• You can compute or retrieve a past time to use in a query by using a function return value as a timestamp or SCN argument. For example, you can perform date and time calculations by adding or subtracting an INTERVAL value to the value of the SYSTIMESTAMP function.

• You can query locally or remotely (Flashback Query, Flashback Version Query, or Flashback Transaction Query). for example here is a remote Flashback Query:

  (SELECT * FROM employees@some_remote_host AS OF 
          TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
  
  

• To ensure database consistency, always perform a COMMIT or ROLLBACK operation before querying past data.

• Remember that all flashback processing is done using the current session settings, such as national language and character set, not the settings that were in effect at the time being queried.

• Some DDLs that alter the structure of a table, such as drop/modify column, move table, drop partition, and truncate table/partition, invalidate any existing undo data for the table. It is not possible to retrieve data from a point earlier than the time such DDLs were executed. Trying such a query results in error ORA-1466. This restriction does not apply to DDL operations that alter the storage attributes of a table, such as PCTFREE, INITRANS, and MAXTRANS.

• Use an SCN to query past data at a precise time. If you use a timestamp, the actual time queried might be up to 3 seconds earlier than the time you specify. Internally, Oracle Database uses SCNs; these are mapped to timestamps at a granularity of every 3 seconds.

  For example, assume that the SCN values 1000 and 1005 are mapped to the times 8:41 and 8:46 AM respectively. A query for a time between 8:41:00 and 8:45:59 AM is mapped to SCN 1000; a Flashback Query for 8:46 AM is mapped to SCN 1005.

  Due to this time-to-SCN mapping, if you specify a time that is slightly after a DDL operation (such as a table creation) the database might actually use an SCN that is just before the DDL operation. This can result in error ORA-1466.

• You cannot retrieve past data from a V$ view in the data dictionary. Performing a query on such a view always returns the current data. You can, however, perform queries on past data in other views of the data dictionary, such as USER_TABLES.