Using Oracle Flashback Technology

左手的ㄟ右手 2023-10-18 23:42 116阅读 0赞

## Using Oracle Flashback Technology ##

This chapter explains how to use Oracle Flashback Technology in database applications.

Topics:

*  [Overview of Oracle Flashback Technology][]
 *  [Configuring Your Database for Oracle Flashback Technology][]
 *  [Using Oracle Flashback Query (SELECT AS OF)][Using Oracle Flashback Query _SELECT AS OF]
 *  [Using Oracle Flashback Version Query][]
 *  [Using Oracle Flashback Transaction Query][]
 *  [Using Oracle Flashback Transaction Query with Oracle Flashback Version Query][]
 *  [Using DBMS\_FLASHBACK Package][Using DBMS_FLASHBACK Package]
 *  [Using Flashback Transaction][]
 *  [Using Flashback Data Archive (Oracle Total Recall)][Using Flashback Data Archive _Oracle Total Recall]
 *  [General Guidelines for Oracle Flashback Technology][]
 *  [Performance Guidelines for Oracle Flashback Technology][]

### Overview of Oracle Flashback Technology ###

Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

With flashback features, you can:

*  Perform queries that return past data
 *  Perform queries that return metadata that shows a detailed history of changes to the database
 *  Recover tables or rows to a previous point in time
 *  Automatically track and archive transactional data changes
 *  Roll back a transaction and its dependent transactions while the database remains online

Oracle Flashback features use the Automatic Undo Management (AUM) system to obtain metadata and historical data for transactions. They rely on undo data, which are records of the effects of individual transactions. For example, if a user runs an `UPDATE` statement to change a salary from 1000 to 1100, then Oracle Database stores the value 1000 in the undo data.

Undo data is persistent and survives a database shutdown. By using flashback features, you can use undo data to query past data or recover from logical damage. Besides using it in flashback features, Oracle Database uses undo data to perform these actions:

*  Roll back active transactions
 *  Recover terminated transactions by using database or process recovery
 *  Provide read consistency for SQL queries

Topics:

*  [Application Development Features][]
 *  [Database Administration Features][]

For additional general information about flashback features, see [Oracle Database Concepts][]

#### Application Development Features ####

In application development, you can use these flashback features to report historical data or undo erroneous changes. (You can also use these features interactively as a database user or administrator.)

Oracle Flashback Query

Use this feature to retrieve data for an earlier time that you specify with the `AS` `OF` clause of the `SELECT` statement. For more information, see ["Using Oracle Flashback Query (SELECT AS OF)"][Using Oracle Flashback Query _SELECT AS OF].

Oracle Flashback Version Query

Use this feature to retrieve metadata and historical data for a specific time interval (for example, to view all the rows of a table that ever existed during a given time interval). Metadata for each row version includes start and end time, type of change operation, and identity of the transaction that created the row version. To create an Oracle Flashback Version Query, use the `VERSIONS` `BETWEEN` clause of the `SELECT` statement. For more information, see ["Using Oracle Flashback Version Query"][Using Oracle Flashback Version Query].

Oracle Flashback Transaction Query

Use this feature to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. To perform an Oracle Flashback Transaction Query, select from the static data dictionary view `FLASHBACK_TRANSACTION_QUERY`. For more information, see ["Using Oracle Flashback Transaction Query"][Using Oracle Flashback Transaction Query].

Typically, you use Oracle Flashback Transaction Query with an Oracle Flashback Version Query that provides the transaction IDs for the rows of interest (see ["Using Oracle Flashback Transaction Query with Oracle Flashback Version Query"][Using Oracle Flashback Transaction Query with Oracle Flashback Version Query]).

DBMS\_FLASHBACK Package

Use this feature to set the internal Oracle Database clock to an earlier time so that you can examine data that was current at that time, or to roll back a transaction and its dependent transactions while the database remains online (see [Flashback Transaction][]). For more information, see ["Using DBMS\_FLASHBACK Package"][Using DBMS_FLASHBACK Package].

Flashback Transaction

Use Flashback Transaction to roll back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and run the corresponding compensating transactions that return the affected data to its original state. (Flashback Transaction is part of `DBMS_FLASHBACK` package.) For more information, see ["Using DBMS\_FLASHBACK Package"][Using DBMS_FLASHBACK Package].

Flashback Data Archive (Oracle Total Recall)

Use Flashback Data Archive to automatically track and archive both regular queries and Oracle Flashback Query, ensuring SQL-level access to the versions of database objects without getting a snapshot-too-old error. For more information, see ["Using Flashback Data Archive (Oracle Total Recall)"][Using Flashback Data Archive _Oracle Total Recall].

#### Database Administration Features ####

These flashback features are primarily for data recovery. Typically, you use these features only as a database administrator.

This chapter focuses on the ["Application Development Features"][Application Development Features]. For more information about the database administration features, see [Oracle Database Administrator's Guide][Oracle Database Administrator_s Guide] and the [Oracle Database Backup and Recovery User's Guide][Oracle Database Backup and Recovery User_s Guide].

Oracle Flashback Table

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

Oracle Flashback Drop

Use this feature to recover a dropped table. This feature reverses the effects of a `DROP` `TABLE` statement.

Oracle Flashback Database

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.

### Configuring Your Database for Oracle Flashback Technology ###

Before you can use flashback features in your application, you or your database administrator must perform the configuration tasks described in these topics:

*  [Configuring Your Database for Automatic Undo Management][]
 *  [Configuring Your Database for Oracle Flashback Transaction Query][]
 *  [Configuring Your Database for Flashback Transaction][]
 *  [Enabling Oracle Flashback Operations on Specific LOB Columns][]
 *  [Granting Necessary Privileges][]

#### Configuring Your Database for Automatic Undo Management ####

To configure your database for Automatic Undo Management (AUM), you or your database administrator must:

*  Create an undo tablespace with enough space to keep the required data for flashback operations.
    
    The more often users update the data, the more space is required. The database administrator usually calculates the space requirement.
 *  Enable AUM, as explained in [Oracle Database Administrator's Guide][Oracle Database Administrator_s Guide 1]. Set these database initialization parameters:
    
     *  `UNDO_MANAGEMENT`
     *  `UNDO_TABLESPACE`
     *  `UNDO_RETENTION`
    
    For a fixed-size undo tablespace, Oracle Database automatically tunes the system to give the undo tablespace the best possible undo retention.
    
    For an automatically extensible undo tablespace, Oracle Database retains undo data longer than the longest query duration and the low threshold of undo retention specified by the `UNDO_RETENTION` parameter.
    
    Note:
    
    You can query `V$UNDOSTAT`.`TUNED_UNDORETENTION` to determine the amount of time for which undo is retained for the current undo tablespace. For more information about `V$UNDOSTAT`, see [Oracle Database Reference][].
    
    Setting `UNDO_RETENTION` does not guarantee that unexpired undo data is not discarded. If the system needs more space, Oracle Database can overwrite unexpired undo with more recently generated undo data.
 *  Specify the `RETENTION` `GUARANTEE` clause for the undo tablespace to ensure that unexpired undo data is not discarded.

See Also:

[Oracle Database Administrator's Guide][Oracle Database Administrator_s Guide 1] for more information about creating an undo tablespace and enabling AUM

#### Configuring Your Database for Oracle Flashback Transaction Query ####

To configure your database for the Oracle Flashback Transaction Query feature, you or your database administrator must:

*  Ensure that Oracle Database is running with version 10.0 compatibility.
 *  Enable supplemental logging:
    
        ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

#### Configuring Your Database for Flashback Transaction ####

To configure your database for the Flashback Transaction feature, you or your database administrator must:

*  With the database mounted but not open, enable `ARCHIVELOG`:
    
        ALTER DATABASE ARCHIVELOG;
 *  Open at least one archive log:
    
        ALTER SYSTEM ARCHIVE LOG CURRENT;
 *  If not done, enable minimal and primary key supplemental logging:
    
        ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
        ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
 *  If you want to track foreign key dependencies, enable foreign key supplemental logging:
    
        ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Note:

If you have very many foreign key constraints, enabling foreign key supplemental logging might not be worth the performance penalty.

#### Enabling Oracle Flashback Operations on Specific LOB Columns ####

To enable flashback operations on specific LOB columns of a table, use the `ALTER` `TABLE` statement with the `RETENTION` option.

Because undo data for LOB columns can be voluminous, you must define which LOB columns to use with flashback operations.

See Also:

[Oracle Database SecureFiles and Large Objects Developer's Guide][Oracle Database SecureFiles and Large Objects Developer_s Guide] to learn about LOB storage and the `RETENTION` parameter

#### Granting Necessary Privileges ####

You or your database administrator must grant privileges to users, roles, or applications that must use these flashback features. For information about the `GRANT` statement, see [Oracle Database SQL Language Reference][].

For Oracle Flashback Query and Oracle Flashback Version Query

To allow access to specific objects during queries, grant `FLASHBACK` and `SELECT` privileges on those objects.

To allow queries on all tables, grant the `FLASHBACK` `ANY` `TABLE` privilege.

For Oracle Flashback Transaction Query

Grant the `SELECT` `ANY` `TRANSACTION` privilege.

To allow execution of undo SQL code retrieved by an Oracle Flashback Transaction Query, grant `SELECT`, `UPDATE`, `DELETE`, and `INSERT` privileges for specific tables.

For DBMS\_FLASHBACK Package

To allow access to the features in the `DBMS_FLASHBACK` package, grant the `EXECUTE` privilege on `DBMS_FLASHBACK`.

For Flashback Data Archive (Oracle Total Recall)

To allow a specific user to enable Flashback Data Archive on tables, using a specific Flashback Data Archive, grant the `FLASHBACK` `ARCHIVE` object privilege on that Flashback Data Archive to that user. To grant the `FLASHBACK` `ARCHIVE` object privilege, you must either be logged on as `SYSDBA` or have `FLASHBACK` `ARCHIVE` `ADMINISTER` system privilege.

To allow execution of these statements, grant the `FLASHBACK` `ARCHIVE` `ADMINISTER` system privilege:

*  `CREATE` `FLASHBACK` `ARCHIVE`
 *  `ALTER` `FLASHBACK` `ARCHIVE`
 *  `DROP` `FLASHBACK` `ARCHIVE`

To grant the `FLASHBACK` `ARCHIVE` `ADMINISTER` system privilege, you must be logged on as `SYSDBA`.

To create a default Flashback Data Archive, using either the `CREATE` `FLASHBACK` `ARCHIVE` or `ALTER` `FLASHBACK` `ARCHIVE` statement, you must be logged on as `SYSDBA`.

To disable Flashback Data Archive for a table that has been enabled for Flashback Data Archive, you must either be logged on as `SYSDBA` or have the `FLASHBACK` `ARCHIVE` `ADMINISTER` system privilege.

### Using Oracle Flashback Query (SELECT AS OF) ###

To use Oracle Flashback Query, use a `SELECT` statement with an `AS` `OF` clause. Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.

Uses of Oracle Flashback Query include:

*  Recovering lost data or undoing incorrect, committed changes.
    
    For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
 *  Comparing current data with the corresponding data at an earlier time.
    
    For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.
 *  Checking the state of transactional data at a particular time.
    
    For example, you can verify the account balance of a certain day.
 *  Simplifying application design by removing the need to store some kinds of temporal data.
    
    Oracle Flashback Query lets you retrieve past data directly from the database.
 *  Applying packaged applications, such as report generation tools, to past data.
 *  Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.

Topics:

*  [Example of Examining and Restoring Past Data][]
 *  [Guidelines for Oracle Flashback Query][]

For more information about the `SELECT` `AS` `OF` statement, see [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 1].

#### Example of Examining and Restoring Past Data ####

Suppose that you discover at 12:30 PM that the row for employee Chung was deleted from the `employees` table, and you know that at 9:30AM the data for Chung was correctly stored in the database. You can use Oracle Flashback Query to examine the contents of the table at 9:30 AM to find out what data was lost. If appropriate, you can restore the lost data.

[Example 12-1][] retrieves the state of the record for `Chung` at 9:30AM, April 4, 2004:

Example 12-1 Retrieving a Lost Row with Oracle Flashback Query

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

[Example 12-2][] restores Chung's information to the `employees` table:

Example 12-2 Restoring a Lost Row After Oracle Flashback Query

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'
    );

#### Guidelines for Oracle Flashback Query ####

*  You can specify or omit the `AS` `OF` clause for each table and specify different times for different tables.
    
    Note:
    
    If a table is a Flashback Data Archive and you specify a time for it that is earlier than its creation time, the query returns zero rows for that table, rather than causing an error. (For information about Flashback Data Archives, see ["Using Flashback Data Archive (Oracle Total Recall)"][Using Flashback Data Archive _Oracle Total Recall].)
 *  You can use the `AS` `OF` clause in queries to perform data definition language (DDL) operations (such as creating and truncating tables) or data manipulation language (DML) statements (such as `INSERT` and `DELETE`) in the same session as Oracle Flashback Query.
 *  To use the result of Oracle 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.
 *  If a possible 3-second error (maximum) is important to Oracle Flashback Query in your application, use an SCN instead of a time stamp. See ["General Guidelines for Oracle Flashback Technology"][General Guidelines for Oracle Flashback Technology].
 *  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, 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 Oracle Flashback Query with a `CREATE` `TABLE` `AS` `SELECT` or `INSERT` `INTO` `TABLE` `SELECT` statement. For example, this 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)
            MINUS SELECT * FROM employees;
    
    `SYSTIMESTAMP` refers to the time zone of the database host environment.

### Using Oracle Flashback Version Query ###

Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a `COMMIT` statement is executed.

Specify Oracle Flashback Version Query using the `VERSIONS` `BETWEEN` clause of the `SELECT` statement. The syntax is:

VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}

where `start` and `end` are expressions representing the start and end, respectively, of the time interval to be queried. The time interval includes (`start`and `end`).

Oracle Flashback Version Query returns a table with a row for each version of the row that existed at any time during the specified time interval. Each row in the table includes pseudocolumns of metadata about the row version, described in [Table 12-1][]. This information can reveal when and how a particular change (perhaps erroneous) occurred to your database.

Table 12-1 Oracle Flashback Version Query Row Data Pseudocolumns

<table> 
 <thead> 
  <tr> 
   <th>Pseudocolumn Name</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <p><code><a rel="nofollow"></a>VERSIONS_STARTSCN</code></p> <p><code><a rel="nofollow"></a>VERSIONS_STARTTIME</code></p> </td> 
   <td> <p>Starting System Change Number (SCN) or&nbsp;<code>TIMESTAMP</code>&nbsp;when the row version was created. This pseudocolumn identifies the time when the data first had the values reflected in the row version. Use this pseudocolumn to identify the past target time for Oracle Flashback Table or Oracle Flashback Query.</p> <p>If this pseudocolumn is&nbsp;<code>NULL</code>, then the row version was created before&nbsp;<code>start</code>.</p> </td> 
  </tr> 
  <tr> 
   <td> <p><code><a rel="nofollow"></a>VERSIONS_ENDSCN</code></p> <p><code><a rel="nofollow"></a>VERSIONS_ENDTIME</code></p> </td> 
   <td> <p>SCN or&nbsp;<code>TIMESTAMP</code>&nbsp;when the row version expired.</p> <p>If this pseudocolumn is&nbsp;<code>NULL</code>, then either the row version was current at the time of the query or the row corresponds to a&nbsp;<code>DELETE</code>&nbsp;operation.</p> </td> 
  </tr> 
  <tr> 
   <td> <p><code><a rel="nofollow"></a>VERSIONS_XID</code></p> </td> 
   <td> <p>Identifier of the transaction that created the row version.</p> </td> 
  </tr> 
  <tr> 
   <td> <p><code><a rel="nofollow"></a>VERSIONS_OPERATION</code></p> </td> 
   <td> <p>Operation performed by the transaction:&nbsp;<code>I</code>&nbsp;for insertion,&nbsp;<code>D</code>&nbsp;for deletion, or&nbsp;<code>U</code>&nbsp;for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an&nbsp;<code>INSERT</code>&nbsp;operation, the row before a&nbsp;<code>DELETE</code>&nbsp;operation, or the row affected by an&nbsp;<code>UPDATE</code>&nbsp;operation.</p> <p>For user updates of an index key, Oracle Flashback Version Query might treat an&nbsp;<code>UPDATE</code>&nbsp;operation as two operations,&nbsp;<code>DELETE</code>&nbsp;plus&nbsp;<code>INSERT</code>, represented as two version rows with a&nbsp;<code>D</code>&nbsp;followed by an&nbsp;<code>I</code><code>VERSIONS_OPERATION</code>.</p> </td> 
  </tr> 
 </tbody> 
</table>

A given row version is valid starting at its time `VERSIONS_START*` up to, but not including, its time `VERSIONS_END*`. That is, it is valid for any time t such that `VERSIONS_START*` <= t < `VERSIONS_END*`. For example, this output indicates that the salary was 10243 from September 9, 2002, included, to November 25, 2003, excluded.

VERSIONS_START_TIME     VERSIONS_END_TIME     SALARY
    -------------------     -----------------     ------
    09-SEP-2003             25-NOV-2003           10243

Here is a typical use of Oracle Flashback Version Query:

SELECT versions_startscn, versions_starttime,
           versions_endscn, versions_endtime,
           versions_xid, versions_operation,
           last_name, salary
      FROM employees
      VERSIONS BETWEEN TIMESTAMP
          TO_TIMESTAMP('2008-12-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
      AND TO_TIMESTAMP('2008-12-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
      WHERE first_name = 'John';

You can use `VERSIONS_XID` with Oracle Flashback Transaction Query to locate this transaction's metadata, including the SQL required to undo the row change and the user responsible for the change—see ["Using Oracle Flashback Transaction Query"][Using Oracle Flashback Transaction Query].

Flashback Version Query allows index-only access only with IOTs (index-organized tables), but index fast full scan is not allowed.

See Also:

[Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 1] for information about Oracle Flashback Version Query pseudocolumns and the syntax of the `VERSIONS` clause

### Using Oracle Flashback Transaction Query ###

Use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the static data dictionary view `FLASHBACK_TRANSACTION_QUERY`, whose columns are described in [Oracle Database Reference][Oracle Database Reference 1].

The column `UNDO_SQL` shows the SQL code that is the logical opposite of the DML operation performed by the transaction. You can usually use this code to reverse the logical steps taken during the transaction. However, there are cases where the `SQL_UNDO` code is not the exact opposite of the original transaction. For example, a `SQL_UNDO` `INSERT` operation might not insert a row back in a table at the same `ROWID` from which it was deleted.

This statement queries the `FLASHBACK_TRANSACTION_QUERY` view for transaction information, including the transaction ID, the operation, the operation start and end SCNs, the user responsible for the operation, and the SQL code that shows the logical opposite of the operation:

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

This statement uses Oracle Flashback Version Query as a subquery to associate each row version with the `LOGON_USER` responsible for the row data change:

SELECT xid, logon_user
    FROM flashback_transaction_query
    WHERE xid IN (
      SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP
      TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
      TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
    );

Note:

If you query `FLASHBACK_TRANSACTION_QUERY` without specifying `XID` in the `WHERE` clause, the query scans many unrelated rows, degrading performance.

See Also:

*  [Oracle Database Backup and Recovery User's Guide.][Oracle Database Backup and Recovery User_s Guide.] for information about how a database administrator can use Flashback Table to restore an entire table, rather than individual rows
 *  [Oracle Database Administrator's Guide][Oracle Database Administrator_s Guide 2] for information about how a database administrator can use Flashback Table to restore an entire table, rather than individual rows

### Using Oracle Flashback Transaction Query with Oracle Flashback Version Query ###

In this example, a database administrator does this:

DROP TABLE emp;
    CREATE TABLE emp (
      empno   NUMBER PRIMARY KEY,
      empname VARCHAR2(16),
      salary  NUMBER
    );
    INSERT INTO emp (empno, empname, salary) VALUES (111, 'Mike', 555);
    COMMIT;
    
    DROP TABLE dept;
    CREATE TABLE dept (
      deptno   NUMBER,
      deptname VARCHAR2(32)
    );
    INSERT INTO dept (deptno, deptname) VALUES (10, 'Accounting');
    COMMIT;

Now `emp` and `dept` have one row each. In terms of row versions, each table has one version of one row. Suppose that an erroneous transaction deletes `empno` `111` from table `emp`:

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

Next, a transaction reinserts `empno` `111` into the `emp` table with a new employee name:

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

The database administrator detects the application error and must diagnose the problem. The database administrator issues this query to retrieve versions of the rows in the `emp` table that correspond to empno `111`. The query uses Oracle Flashback Version Query pseudocolumns:

SELECT versions_xid XID, versions_startscn START_SCN,
      versions_endscn END_SCN, versions_operation OPERATION,
      empname, salary
    FROM emp
    VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
    WHERE empno = 111;

Results are similar to:

XID               START_SCN    END_SCN O EMPNAME              SALARY
    ---------------- ---------- ---------- - ---------------- ----------
    09001100B2200000   10093466            I Tom                     927
    030002002B210000   10093459            D Mike                    555
    0800120096200000   10093375   10093459 I Mike                    555
     
    3 rows selected.

The results table rows are in descending chronological order. The third row corresponds to the version of the row in the table `emp` that was inserted in the table when the table was created. The second row corresponds to the row in `emp` that the erroneous transaction deleted. The first row corresponds to the version of the row in `emp` that was reinserted with a new employee name.

The database administrator identifies transaction `030002002B210000` as the erroneous transaction and uses Oracle Flashback Transaction Query to audit all changes made by this transaction:

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

Results are similar to:

XID               START_SCN COMMIT_SCN OPERATION LOGON_USER
    ---------------- ---------- ---------- --------- ------------------------------
    UNDO_SQL
    --------------------------------------------------------------------------------
     
    030002002B210000   10093452   10093459 DELETE    HR
    insert into "HR"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');
     
    030002002B210000   10093452   10093459 INSERT    HR
    delete from "HR"."DEPT" where ROWID = 'AAATjuAAEAAAAJrAAB';
     
    030002002B210000   10093452   10093459 UPDATE    HR
    update "HR"."EMP" set "SALARY" = '555' where ROWID = 'AAATjsAAEAAAAJ7AAA';
     
    030002002B210000   10093452   10093459 BEGIN     HR
     
     
    4 rows selected.

To make the result of the next query easier to read, the database administrator uses these SQL\*Plus commands:

COLUMN operation FORMAT A9
    COLUMN table_name FORMAT A10
    COLUMN table_owner FORMAT A11

To see the details of the erroneous transaction and all subsequent transactions, the database administrator performs this query:

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');

Results are similar to:

XID               START_SCN COMMIT_SCN OPERATION TABLE_NAME TABLE_OWNER
    ---------------- ---------- ---------- --------- ---------- -----------
    02000E0074200000   10093435   10093446 INSERT    DEPT       HR
    030002002B210000   10093452   10093459 DELETE    EMP        HR
    030002002B210000   10093452   10093459 INSERT    DEPT       HR
    030002002B210000   10093452   10093459 UPDATE    EMP        HR
    0800120096200000   10093374   10093375 INSERT    EMP        HR
    09001100B2200000   10093462   10093466 UPDATE    EMP        HR
    09001100B2200000   10093462   10093466 UPDATE    EMP        HR
    09001100B2200000   10093462   10093466 INSERT    EMP        HR
     
    8 rows selected.

Note:

Because the preceding query does not specify the `XID` in the `WHERE` clause, it scans many unrelated rows, degrading performance.

### Using DBMS\_FLASHBACK Package ###

The `DBMS_FLASHBACK` package provides the same functionality as Oracle Flashback Query, but Oracle Flashback Query is sometimes more convenient.

The `DBMS_FLASHBACK` package acts as a time machine: you can turn back the clock, perform normal queries as if you were at that earlier time, and then return to the present. Because you can use the `DBMS_FLASHBACK` package to perform queries on past data without special clauses such as `AS` `OF` or `VERSIONS` `BETWEEN`, you can reuse existing PL/SQL code to query the database at earlier times.

You must have the `EXECUTE` privilege on the `DBMS_FLASHBACK` package.

To use the `DBMS_FLASHBACK` package in your PL/SQL code:

1.  Specify a past time by invoking either `DBMS_FLASHBACK`.`ENABLE_AT_TIME` or `DBMS_FLASHBACK`.`ENABLE_AT_SYSTEM_CHANGE_NUMBER`.
2.  Perform regular queries (that is, queries without special flashback-feature syntax such as `AS` `OF`). Do not perform DDL or DML operations.
    
    The database is queried at the specified past time.
3.  Return to the present by invoking `DBMS_FLASHBACK.DISABLE`.
    
    You must invoke `DBMS_FLASHBACK`.`DISABLE` before invoking `DBMS_FLASHBACK`.`ENABLE_AT_TIME` or `DBMS_FLASHBACK`.`ENABLE_AT_SYSTEM_CHANGE_NUMBER` again. You cannot nest enable/disable pairs.

To use a cursor to store the results of queries, open the cursor before invoking `DBMS_FLASHBACK`.`DISABLE`. After storing the results and invoking `DBMS_FLASHBACK`.`DISABLE`, you can:

*  Perform `INSERT` or `UPDATE` operations to modify the current database state by using the stored results from the past.
 *  Compare current data with the past data. After invoking `DBMS_FLASHBACK`.`DISABLE`, open a second cursor. Fetch from the first cursor to retrieve past data; fetch from the second cursor to retrieve current data. You can store the past data in a temporary table and then use set operators such as `MINUS` or `UNION` to contrast or combine the past and current data.

You can invoke `DBMS_FLASHBACK`.`GET_SYSTEM_CHANGE_NUMBER` at any time to get the current System Change Number (SCN). `DBMS_FLASHBACK`.`GET_SYSTEM_CHANGE_NUMBER` always returns the current SCN regardless of previous invocations of `DBMS_FLASHBACK`.`ENABLE`.

See Also:

*  [Oracle Database PL/SQL Packages and Types Reference][Oracle Database PL_SQL Packages and Types Reference] for details of the `DBMS_FLASHBACK` package

### Using Flashback Transaction ###

The `DBMS_FLASHBACK`.`TRANSACTION_BACKOUT` procedure rolls back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and run the compensating transactions that return the affected data to its original state.

The transactions being rolled back are subject to these restrictions:

*  They cannot have performed DDL operations that changed the logical structure of database tables.
 *  They cannot use Large Object (LOB) Data Types:
    
     *  `BFILE`
     *  `BLOB`
     *  `CLOB`
     *  `NCLOB`
 *  They cannot use features that LogMiner does not support.
    
    The features that LogMiner supports depends on the value of the `COMPATIBLE` initialization parameter for the database that is rolling back the transaction. The default value is the release number of the most recent major release.
    
    Flashback Transaction inherits SQL data type support from LogMiner. Therefore, if LogMiner fails due to an unsupported SQL data type in a the transaction, Flashback Transaction fails too.
    
    Some data types, though supported by LogMiner, do not generate undo information as part of operations that modify columns of such types. Therefore, Flashback Transaction does not support tables containing these data types. These include tables with BLOB, CLOB and XML type.

See Also:

*  [Oracle Data Guard Concepts and Administration][] for information about data type and DDL support on a logical standby database
 *  [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 2] for information about LOB data types
 *  [Oracle Database Utilities][] for information about LogMiner
 *  [Oracle Database Administrator's Guide][Oracle Database Administrator_s Guide 3] for information about the `COMPATIBLE` initialization parameter

Topics:

*  [Dependent Transactions][]
 *  [TRANSACTION\_BACKOUT Parameters][TRANSACTION_BACKOUT Parameters]
 *  [TRANSACTION\_BACKOUT Reports][TRANSACTION_BACKOUT Reports]

#### Dependent Transactions ####

In the context of Flashback Transaction, transaction 2 can depend on transaction 1 in any of these ways:

*  Write-after-write dependency
    
    Transaction 1 changes a row of a table, and later transaction 2 changes the same row.
 *  Primary key dependency
    
    A table has a primary key constraint on column c. In a row of the table, column c has the value v. Transaction 1 deletes that row, and later transaction 2 inserts a row into the same table, assigning the value v to column c.
 *  Foreign key dependency
    
    In table b, column b1 has a foreign key constraint on column a1 of table a. Transaction 1 changes a value in a1, and later transaction 2 changes a value in b1.

#### TRANSACTION\_BACKOUT Parameters ####

The parameters of the `TRANSACTION_BACKOUT` procedure are:

*  Number of transactions to be backed out
 *  List of transactions to be backed out, identified either by name or by XID
 *  Time hint, if you identify transactions by name
    
    Specify a time that is earlier than any transaction started.
 *  Backout option from [Table 12-2][]

For the syntax of the `TRANSACTION_BACKOUT` procedure and detailed parameter descriptions, see [Oracle Database PL/SQL Packages and Types Reference][Oracle Database PL_SQL Packages and Types Reference].

Table 12-2 Flashback TRANSACTION\_BACKOUT Options

<table> 
 <thead> 
  <tr> 
   <th>Option</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <p><code>CASCADE</code></p> </td> 
   <td> <p>Backs out specified transactions and all dependent transactions in a post-order fashion (that is, children are backed out before parents are backed out).</p> <p>Without&nbsp;<code>CASCADE</code>, if any dependent transaction is not specified, an error occurs.</p> </td> 
  </tr> 
  <tr> 
   <td> <p><code>NOCASCADE</code></p> </td> 
   <td> <p>Default. Backs out specified transactions, which are expected to have no dependent transactions. First dependent transactions causes an error and appears in&nbsp;<code>*_FLASHBACK_TXN_REPORT</code>.</p> </td> 
  </tr> 
  <tr> 
   <td> <p><code>NOCASCADE_FORCE</code></p> </td> 
   <td> <p>Backs out specified transactions, ignoring dependent transactions. Server runs undo SQL statements for specified transactions in reverse order of commit times.</p> <p>If no constraints break and you are satisfied with the result, you can commit the changes; otherwise, you can roll them back.</p> </td> 
  </tr> 
  <tr> 
   <td> <p><code>NONCONFLICT_ONLY</code></p> </td> 
   <td> <p>Backs out changes to nonconflicting rows of the specified transactions. Database remains consistent, but transaction atomicity is lost.</p> </td> 
  </tr> 
 </tbody> 
</table>

`TRANSACTION_BACKOUT` analyzes the transactional dependencies, performs DML operations, and generates reports. `TRANSACTION_BACKOUT` does not commit the DML operations that it performs as part of transaction backout, but it holds all the required locks on rows and tables in the right form, preventing other dependencies from entering the system. To make the transaction backout permanent, you must explicitly commit the transaction.

#### TRANSACTION\_BACKOUT Reports ####

To see the reports that `TRANSACTION_BACKOUT` generates, query the static data dictionary views `*_FLASHBACK_TXN_STATE` and `*_FLASHBACK_TXN_REPORT`.

\*\_FLASHBACK\_TXN\_STATE

The static data dictionary view `*_FLASHBACK_TXN_STATE` shows whether a transaction is active or backed out. If a transaction appears in this view, it is backed out.

`*_FLASHBACK_TXN_STATE` is maintained atomically for compensating transactions. If a compensating transaction is backed out, all changes that it made are also backed out, and `*_FLASHBACK_TXN_STATE` reflects this. For example, if compensating transaction `ct` backs out transactions `t1` and `t2`, then `t1`and `t2` appear in `*_FLASHBACK_TXN_STATE`. If `ct` itself is later backed out, the effects of `t1` and `t2` are reinstated, and `t1` and `t2` disappear from `*_FLASHBACK_TXN_STATE`.

See Also:

[Oracle Database Reference][Oracle Database Reference 2] for more information about `*_FLASHBACK_TXN_STATE`

\*\_FLASHBACK\_TXN\_REPORT

The static data dictionary view `*_FLASHBACK_TXN_REPORT` provides a detailed report for each backed-out transaction.

See Also:

[Oracle Database Reference][Oracle Database Reference 3] for more information about `*_FLASHBACK_TXN_REPORT`

### Using Flashback Data Archive (Oracle Total Recall) ###

A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

A Flashback Data Archive consists of one or more tablespaces or parts thereof. You can have multiple Flashback Data Archives. If you are logged on as `SYSDBA`, you can specify a default Flashback Data Archive for the system. A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.

By default, flashback archiving is off for any table. You can enable flashback archiving for a table if all of these conditions are true:

*  You have the `FLASHBACK` `ARCHIVE` object privilege on the Flashback Data Archive to use for that table.
 *  The table is neither nested, clustered, temporary, remote, or external.
 *  The table contains neither `LONG` nor nested columns.
 *  The table does not use any of these Flashback Data Archive reserved words as column names:
    
     *  `STARTSCN`
     *  `ENDSCN`
     *  `RID`
     *  `XID`
     *  `OP`
     *  `OPERATION`

After flashback archiving is enabled for a table, you can disable it only if you either have the `FLASHBACK` `ARCHIVE` `ADMINISTER` system privilege or you are logged on as `SYSDBA`.

When choosing a Flashback Data Archive for a specific table, consider the data retention requirements for the table and the retention times of the Flashback Data Archives on which you have the `FLASHBACK` `ARCHIVE` object privilege.

Topics:

*  [Creating a Flashback Data Archive][]
 *  [Altering a Flashback Data Archive][]
 *  [Dropping a Flashback Data Archive][]
 *  [Specifying the Default Flashback Data Archive][]
 *  [Enabling and Disabling Flashback Data Archive][]
 *  [DDL Statements on Tables Enabled for Flashback Data Archive][]
 *  [Viewing Flashback Data Archive Data][]
 *  [Flashback Data Archive Scenarios][]

See Also:

`http://www.oracle.com/database/total-recall.html` for more information about Oracle Total Recall

#### Creating a Flashback Data Archive ####

Create a Flashback Data Archive with the `CREATE` `FLASHBACK` `ARCHIVE` statement, specifying:

*  Name of the Flashback Data Archive
 *  Name of the first tablespace of the Flashback Data Archive
 *  (Optional) Maximum amount of space that the Flashback Data Archive can use in the first tablespace
    
    The default is unlimited. Unless your space quota on the first tablespace is also unlimited, you must specify this value; otherwise, error ORA-55621 occurs.
 *  Retention time (number of days that Flashback Data Archive data for the table is guaranteed to be stored)
 *  As of Oracle Database 11g Release 2 (11.2.0.4): (Optional) Whether to optimize the storage of data in the history tables maintained in the Flashback Data Archive, using `[NO]` `OPTIMIZE` `DATA`.
    
    The default is `NO` `OPTIMIZE` `DATA`.

If you are logged on as `SYSDBA`, you can also specify that this is the default Flashback Data Archive for the system. If you omit this option, you can still make this Flashback Data Archive the default later (see ["Specifying the Default Flashback Data Archive"][Specifying the Default Flashback Data Archive]).

Examples

*  Create a default Flashback Data Archive named `fla1` that uses up to 10 G of tablespace `tbs1`, whose data are retained for one year:
    
        CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1
          QUOTA 10G RETENTION 1 YEAR;
 *  Create a Flashback Data Archive named `fla2` that uses tablespace `tbs2`, whose data are retained for two years:
    
        CREATE FLASHBACK ARCHIVE fla2 TABLESPACE tbs2 RETENTION 2 YEAR;

For more information about the `CREATE` `FLASHBACK` `ARCHIVE` statement, see [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 3].

#### Altering a Flashback Data Archive ####

With the `ALTER` `FLASHBACK` `ARCHIVE` statement, you can:

*  Change the retention time of a Flashback Data Archive
 *  Purge some or all of its data
 *  Add, modify, and remove tablespaces
    
    Note:
    
    Removing all tablespaces of a Flashback Data Archive causes an error.

If you are logged on as `SYSDBA`, you can also use the `ALTER` `FLASHBACK` `ARCHIVE` statement to make a specific file the default Flashback Data Archive for the system.

Examples

*  Make Flashback Data Archive `fla1` the default Flashback Data Archive:
    
        ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;
 *  To Flashback Data Archive `fla1`, add up to 5 G of tablespace `tbs3`:
    
        ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G;
 *  To Flashback Data Archive `fla1`, add as much of tablespace `tbs4` as needed:
    
        ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs4;
 *  Change the maximum space that Flashback Data Archive `fla1` can use in tablespace `tbs3` to 20 G:
    
        ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs3 QUOTA 20G;
 *  Allow Flashback Data Archive `fla1` to use as much of tablespace `tbs1` as needed:
    
        ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs1;
 *  Change the retention time for Flashback Data Archive `fla1` to two years:
    
        ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;
 *  Remove tablespace `tbs2` from Flashback Data Archive `fla1`:
    
        ALTER FLASHBACK ARCHIVE fla1 REMOVE TABLESPACE tbs2;
    
    (Tablespace `tbs2` is not dropped.)
 *  Purge all historical data from Flashback Data Archive `fla1`:
    
        ALTER FLASHBACK ARCHIVE fla1 PURGE ALL;
 *  Purge all historical data older than one day from Flashback Data Archive `fla1`:
    
        ALTER FLASHBACK ARCHIVE fla1
          PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
 *  Purge all historical data older than SCN 728969 from Flashback Data Archive `fla1`:
    
        ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE SCN 728969;

For more information about the `ALTER` `FLASHBACK` `ARCHIVE` statement, see [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 4].

#### Dropping a Flashback Data Archive ####

Drop a Flashback Data Archive with the `DROP` `FLASHBACK` `ARCHIVE` statement. Dropping a Flashback Data Archive deletes its historical data, but does not drop its tablespaces.

Example

Remove Flashback Data Archive `fla1` and all its historical data, but not its tablespaces:

DROP FLASHBACK ARCHIVE fla1;

For more information about the `DROP` `FLASHBACK` `ARCHIVE` statement, see [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 5].

#### Specifying the Default Flashback Data Archive ####

By default, the system has no default Flashback Data Archive. If you are logged on as `SYSDBA`, you can specify default Flashback Data Archive in either of these ways:

*  Specify the name of an existing Flashback Data Archive in the `SET` `DEFAULT` clause of the `ALTER` `FLASHBACK` `ARCHIVE` statement. For example:
    
        ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;
    
    If `fla1` does not exist, an error occurs.
 *  Include `DEFAULT` in the `CREATE` `FLASHBACK` `ARCHIVE` statement when you create a Flashback Data Archive. For example:
    
        CREATE FLASHBACK ARCHIVE DEFAULT fla2 TABLESPACE tbs1
          QUOTA 10G RETENTION 1 YEAR;

The default Flashback Data Archive for the system is the default Flashback Data Archive for every user who does not have his or her own default Flashback Data Archive.

See Also:

*  [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 3] for more information about the `CREATE` `FLASHBACK` `ARCHIVE` statement
 *  [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 6] for more information about the `ALTER` `DATABASE` statement

#### Enabling and Disabling Flashback Data Archive ####

By default, flashback archiving is disabled for any table. You can enable flashback archiving for a table if you have the `FLASHBACK` `ARCHIVE` object privilege on the Flashback Data Archive to use for that table.

To enable flashback archiving for a table, include the `FLASHBACK` `ARCHIVE` clause in either the `CREATE` `TABLE` or `ALTER` `TABLE` statement. In the `FLASHBACK` `ARCHIVE` clause, you can specify the Flashback Data Archive where the historical data for the table are stored. The default is the default Flashback Data Archive for the system. If you specify a nonexistent Flashback Data Archive, an error occurs.

If you enable flashback archiving for a table, but AUM is disabled, error ORA-55614 occurs when you try to modify the table.

If a table has flashback archiving enabled, and you try to enable it again with a different Flashback Data Archive, an error occurs.

After flashback archiving is enabled for a table, you can disable it only if you either have the `FLASHBACK` `ARCHIVE` `ADMINISTER` system privilege or you are logged on as `SYSDBA`. To disable flashback archiving for a table, specify `NO` `FLASHBACK` `ARCHIVE` in the `ALTER` `TABLE` statement. (It is unnecessary to specify `NO` `FLASHBACK` `ARCHIVE` in the `CREATE` `TABLE` statement, because that is the default.)

See Also:

[Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 7] for more information about the `FLASHBACK` `ARCHIVE` clause of the `CREATE` `TABLE` statement, including restrictions on its use

Examples

*  Create table `employee` and store the historical data in the default Flashback Data Archive:
    
        CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),
          JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE;
 *  Create table `employee` and store the historical data in the Flashback Data Archive `fla1`:
    
        CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),
          JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE fla1;
 *  Enable flashback archiving for the table `employee` and store the historical data in the default Flashback Data Archive:
    
        ALTER TABLE employee FLASHBACK ARCHIVE;
 *  Enable flashback archiving for the table `employee` and store the historical data in the Flashback Data Archive `fla1`:
    
        ALTER TABLE employee FLASHBACK ARCHIVE fla1;
 *  Disable flashback archiving for the table `employee`:
    
        ALTER TABLE employee NO FLASHBACK ARCHIVE;

#### DDL Statements on Tables Enabled for Flashback Data Archive ####

Flashback Data Archive supports only these DDL statements:

*  `ALTER` `TABLE` statement that does any of the following:
    
     *  Adds, drops, renames, or modifies a column
     *  Adds, drops, or renames a constraint
     *  Drops or truncates a partition or subpartition operation
 *  `TRUNCATE` `TABLE` statement
 *  `RENAME` statement that renames a table

Flashback Data Archive does not support DDL statements that move, split, merge, or coalesce partitions or subpartitions, move tables, or convert `LONG`columns to `LOB` columns.

For example, the following DDL statements cause error ORA-55610 when used on a table enabled for Flashback Data Archive:

*  `ALTER` `TABLE` statement that includes an `UPGRADE` `TABLE` clause, with or without an `INCLUDING` `DATA` clause
 *  `ALTER` `TABLE` statement that moves or exchanges a partition or subpartition operation
 *  `DROP` `TABLE` statement

If you must use unsupported DDL statements on a table enabled for Flashback Data Archive, use the `DBMS_FLASHBACK_ARCHIVE`.`DISASSOCIATE_FBA`procedure to disassociate the base table from its Flashback Data Archive. To reassociate the Flashback Data Archive with the base table afterward, use the `DBMS_FLASHBACK_ARCHIVE`.`REASSOCIATE_FBA` procedure.

See Also:

*  [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 8] for information about the `ALTER` `TABLE` statement
 *  [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 9] for information about the `TRUNCATE` `TABLE` statement
 *  [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 10] for information about the `RENAME` statement
 *  [Oracle Database SQL Language Reference][Oracle Database SQL Language Reference 11] for information about the `DROP` `TABLE` statement
 *  [Oracle Database PL/SQL Packages and Types Reference][Oracle Database PL_SQL Packages and Types Reference 1] for information about the `DBMS_FLASHBACK_ARCHIVE` package

#### Viewing Flashback Data Archive Data ####

[Table 12-3][] lists and briefly describes the static data dictionary views that you can query for information about Flashback Data Archive files.

Table 12-3 Static Data Dictionary Views for Flashback Data Archive Files

<table> 
 <thead> 
  <tr> 
   <th>View</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <p><code>*_FLASHBACK_ARCHIVE</code></p> </td> 
   <td> <p>Displays information about Flashback Data Archive files.</p> </td> 
  </tr> 
  <tr> 
   <td> <p><code>*_FLASHBACK_ARCHIVE_TS</code></p> </td> 
   <td> <p>Displays tablespaces of Flashback Data Archive files.</p> </td> 
  </tr> 
  <tr> 
   <td> <p><code>*_FLASHBACK_ARCHIVE_TABLES</code></p> </td> 
   <td> <p>Displays information about tables that are enabled for Data Flashback Archive files.</p> </td> 
  </tr> 
 </tbody> 
</table>

See Also:

*  [Oracle Database Reference][Oracle Database Reference 4] for detailed information about `*_FLASHBACK_ARCHIVE`
 *  [Oracle Database Reference][Oracle Database Reference 5] for detailed information about `*_FLASHBACK_ARCHIVE_TS`
 *  [Oracle Database Reference][Oracle Database Reference 6] for detailed information about `*_FLASHBACK_ARCHIVE_TABLES`

#### Flashback Data Archive Scenarios ####

*  [Scenario: Using Flashback Data Archive to Enforce Digital Shredding][Scenario_ Using Flashback Data Archive to Enforce Digital Shredding]
 *  [Scenario: Using Flashback Data Archive to Access Historical Data][Scenario_ Using Flashback Data Archive to Access Historical Data]
 *  [Scenario: Using Flashback Data Archive to Generate Reports][Scenario_ Using Flashback Data Archive to Generate Reports]
 *  [Scenario: Using Flashback Data Archive for Auditing][Scenario_ Using Flashback Data Archive for Auditing]
 *  [Scenario: Using Flashback Data Archive to Recover Data][Scenario_ Using Flashback Data Archive to Recover Data]

Scenario: Using Flashback Data Archive to Enforce Digital Shredding

Your company wants to "shred" (delete) historical data changes to the `Taxes` table after ten years. When you create the Flashback Data Archive for `Taxes`, you specify a retention time of ten years:

CREATE FLASHBACK ARCHIVE taxes_archive TABLESPACE tbs1 RETENTION 10 YEAR;

When history data from transactions on `Taxes` exceeds the age of ten years, it is purged. (The `Taxes` table itself, and history data from transactions less than ten years old, are not purged.)

Scenario: Using Flashback Data Archive to Access Historical Data

You want to be able to retrieve the inventory of all items at the beginning of the year from the table `inventory`, and to be able to retrieve the stock price for each symbol in your portfolio at the close of business on any specified day of the year from the table `stock_data`.

Create a default Flashback Data Archive named `fla1` that uses up to 10 G of tablespace `tbs1`, whose data are retained for five years (you must be logged on as `SYSDBA`):

CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1
      QUOTA 10G RETENTION 5 YEAR;

Enable Flashback Data Archive for the tables `inventory` and `stock_data`, and store the historical data in the default Flashback Data Archive:

ALTER TABLE inventory FLASHBACK ARCHIVE;
    ALTER TABLE stock_data FLASHBACK ARCHIVE;

To retrieve the inventory of all items at the beginning of the year 2007, use this query:

SELECT product_number, product_name, count FROM inventory AS OF
      TIMESTAMP TO_TIMESTAMP ('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

To retrieve the stock price for each symbol in your portfolio at the close of business on July 23, 2007, use this query:

SELECT symbol, stock_price FROM stock_data AS OF
      TIMESTAMP TO_TIMESTAMP ('2007-07-23 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
      WHERE symbol IN my_portfolio;

Scenario: Using Flashback Data Archive to Generate Reports

You want users to be able to generate reports from the table `investments`, for data stored in the past five years.

Create a default Flashback Data Archive named `fla2` that uses up to 20 G of tablespace `tbs1`, whose data are retained for five years (you must be logged on as `SYSDBA`):

CREATE FLASHBACK ARCHIVE DEFAULT fla2 TABLESPACE tbs1
      QUOTA 20G RETENTION 5 YEAR;

Enable Flashback Data Archive for the table `investments`, and store the historical data in the default Flashback Data Archive:

ALTER TABLE investments FLASHBACK ARCHIVE;

Lisa wants a report on the performance of her investments at the close of business on December 31, 2006. She uses this query:

SELECT * FROM investments AS OF
      TIMESTAMP TO_TIMESTAMP ('2006-12-31 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
      WHERE name = 'LISA';

Scenario: Using Flashback Data Archive for Auditing

A medical insurance company must audit a medical clinic. The medical insurance company has its claims in the table `Billings`, and creates a default Flashback Data Archive named `fla4` that uses up to 100 G of tablespace `tbs1`, whose data are retained for 10 years:

CREATE FLASHBACK ARCHIVE DEFAULT fla4 TABLESPACE tbs1
      QUOTA 100G RETENTION 10 YEAR;

The company enables Flashback Data Archive for the table `Billings`, and stores the historical data in the default Flashback Data Archive:

ALTER TABLE Billings FLASHBACK ARCHIVE;

On May 1, 2007, clients were charged the wrong amounts for some diagnoses and tests. To see the records as of May 1, 2007, the company uses this query:

SELECT date_billed, amount_billed, patient_name, claim_Id,
      test_costs, diagnosis FROM Billings AS OF TIMESTAMP
      TO_TIMESTAMP('2007-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

Scenario: Using Flashback Data Archive to Recover Data

An end user recovers from erroneous transactions that were previously committed in the database. The undo data for the erroneous transactions is no longer available, but because the required historical information is available in the Flashback Data Archive, Flashback Query works seamlessly.

Lisa manages a software development group whose product sales are doing well. On November 3, 2007, she decides to give all her level-three employees who have more than two years of experience a salary increase of 10% and a promotion to level four. Lisa asks her HR representative, Bob, to make the changes.

Using the HR web application, Bob updates the `employee` table to give Lisa's level-three employees a 10% raise and a promotion to level four. Then Bob finishes his work for the day and leaves for home, unaware that he omitted the requirement of two years of experience in his transaction. A few days later, Lisa checks to see if Bob has done the updates and finds that everyone in the group was given a raise! She calls Bob immediately and asks him to correct the error.

At first, Bob thinks he cannot return the employee table to its prior state without going to the backups. Then he remembers that the `employee` table has Flashback Data Archive enabled.

First, he verifies that no other transaction modified the `employee` table after his: The commit time stamp from the transaction query corresponds to Bob's transaction, two days ago.

Next, Bob uses these statements to return the `employee` table to the way it was before his erroneous change:

DELETE EMPLOYEE WHERE MANAGER = 'LISA JOHNSON';
    INSERT INTO EMPLOYEE
      SELECT * FROM EMPLOYEE
        AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' DAY)
          WHERE MANAGER = 'LISA JOHNSON';

Bob then reexecutes the update that Lisa had requested.

### General Guidelines for Oracle Flashback Technology ###

*  Use the `DBMS_FLASHBACK`.`ENABLE` and `DBMS_FLASHBACK`.`DISABLE` procedures around SQL code that you do not control, or when you want to use the same past time for several consecutive queries.
 *  Use Oracle Flashback Query, Oracle Flashback Version Query, or Oracle Flashback Transaction Query for SQL code that you write, for convenience. An Oracle 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`.
 *  To compute or retrieve a past time to use in a query, use a function return value as a time stamp or SCN argument. For example, add or subtract an `INTERVAL` value to the value of the `SYSTIMESTAMP` function.
 *  Use Oracle Flashback Query, Oracle Flashback Version Query, and Oracle Flashback Transaction Query locally or remotely. An example of a remote Oracle Flashback Query is:
    
        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 uses the current session settings, such as national language and character set, not the settings that were in effect at the time being queried.
 *  Remember that DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for the table. If you try to retrieve data from a time before such a DDL executed, error ORA-01466 occurs. DDL operations that alter the storage attributes of a table (such as `PCTFREE`, `INITRANS`, and `MAXTRANS`) do not invalidate undo data.
 *  To query past data at a precise time, use an SCN. If you use a time stamp, the actual time queried might be up to 3 seconds earlier than the time you specify. Oracle Database uses SCNs internally and maps them to time stamps at a granularity of 3 seconds.
    
    For example, suppose that the SCN values 1000 and 1005 are mapped to the time stamps 8:41 AM and 8:46 AM, respectively. A query for a time between 8:41:00 and 8:45:59 AM is mapped to SCN 1000; an Oracle Flashback Query for 8:46 AM is mapped to SCN 1005. Therefore, if you specify a time that is slightly after a DDL operation (such as a table creation) Oracle Database might use an SCN that is just before the DDL operation, causing error ORA-01466.
 *  You cannot retrieve past data from a dynamic performance (`V$`) view. A query on such a view always returns current data.
 *  You can perform queries on past data in static data dictionary views, such as `*_TABLES`.
 *  As of Oracle Database 11g Release 2 (11.2.0.4), you can enable optimization of data storage for history tables maintained by Flashback Data Archive by specifying `OPTIMIZE` `DATA` when creating or altering a Flashback Data Archive.
    
    `OPTIMIZE` `DATA` optimizes the storage of data in history tables by using any of these features:
    
     *  Advanced Row Compression
     *  Advanced LOB Compression
     *  Advanced LOB Deduplication
     *  Segment-level compression tiering
     *  Row-level compression tiering
    
    The default is not to optimize the storage of data in history tables.

### Performance Guidelines for Oracle Flashback Technology ###

*  Use the `DBMS_STATS` package to generate statistics for all tables involved in an Oracle Flashback Query. Keep the statistics current. Oracle Flashback Query uses the cost-based optimizer, which relies on these statistics.
 *  Minimize the amount of undo data that must be accessed. Use queries to select small sets of past data using indexes, not to scan entire tables. If you must scan a full table, add 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 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 operations are CPU-bound.
    
    It is recommended that you have enough buffer cache, so that the versions query for the archiver finds the undo data in the buffer cache. Buffer cache access is significantly faster than disk access.
 *  For Oracle Flashback Version Query, use index structures. Oracle Database keeps undo data for index changes and data changes. Performance of index lookup-based Oracle Flashback Version Query is an order of magnitude faster than the full table scans that are otherwise needed.
 *  In an Oracle Flashback Transaction Query, the `xid` column is of the type `RAW(8)`. To take advantage of the index built on the `xid` column, use the `HEXTORAW` conversion function: `HEXTORAW(xid)`.
 *  An Oracle Flashback Query against a materialized view does not take advantage of query rewrite optimization.

[Overview of Oracle Flashback Technology]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#i1009447
[Configuring Your Database for Oracle Flashback Technology]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#i1008155
[Using Oracle Flashback Query _SELECT AS OF]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#i1008579
[Using Oracle Flashback Version Query]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#i1019938
[Using Oracle Flashback Transaction Query]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#i1007455
[Using Oracle Flashback Transaction Query with Oracle Flashback Version Query]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFBFGCC
[Using DBMS_FLASHBACK Package]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#i1017341
[Using Flashback Transaction]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFIHEIA
[Using Flashback Data Archive _Oracle Total Recall]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFFDCEH
[General Guidelines for Oracle Flashback Technology]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#g1703520
[Performance Guidelines for Oracle Flashback Technology]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#g1703502
[Application Development Features]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#g1025538
[Database Administration Features]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#g1025551
[Oracle Database Concepts]: https://docs.oracle.com/cd/E11882_01/server.112/e40540/cncptdba.htm#CNCPT1439
[Flashback Transaction]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFIIEAA
[Oracle Database Administrator_s Guide]: https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN015
[Oracle Database Backup and Recovery User_s Guide]: https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmflash.htm#BRADV80055
[Configuring Your Database for Automatic Undo Management]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFDJCJD
[Configuring Your Database for Oracle Flashback Transaction Query]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFGJHCJ
[Configuring Your Database for Flashback Transaction]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFHBGGE
[Enabling Oracle Flashback Operations on Specific LOB Columns]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFJDBAJ
[Granting Necessary Privileges]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFJAHBJ
[Oracle Database Administrator_s Guide 1]: https://docs.oracle.com/cd/E11882_01/server.112/e25494/undo.htm#ADMIN013
[Oracle Database Reference]: https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3118.htm#REFRN30295
[Oracle Database SecureFiles and Large Objects Developer_s Guide]: https://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB101
[Oracle Database SQL Language Reference]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#SQLRF01603
[Example of Examining and Restoring Past Data]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#g1025568
[Guidelines for Oracle Flashback Query]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#g1025584
[Oracle Database SQL Language Reference 1]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#SQLRF01702
[Example 12-1]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BABHIHFF
[Example 12-2]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BABJJCFE
[Table 12-1]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#g1020732
[Oracle Database Reference 1]: https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5125.htm#REFRN29086
[Oracle Database Backup and Recovery User_s Guide.]: https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmflash.htm#BRADV81517
[Oracle Database Administrator_s Guide 2]: https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN01512
[Oracle Database PL_SQL Packages and Types Reference]: https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_flashb.htm#ARPLS142
[Oracle Data Guard Concepts and Administration]: https://docs.oracle.com/cd/E11882_01/server.112/e41134/data_support.htm#SBYDB00305
[Oracle Database SQL Language Reference 2]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF50994
[Oracle Database Utilities]: https://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL019
[Oracle Database Administrator_s Guide 3]: https://docs.oracle.com/cd/E11882_01/server.112/e25494/create.htm#ADMIN11112
[Dependent Transactions]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#CHDIEIGE
[TRANSACTION_BACKOUT Parameters]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFFDAEJ
[TRANSACTION_BACKOUT Reports]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFHDDBG
[Table 12-2]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#g4913725
[Oracle Database Reference 2]: https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_3193.htm#REFRN23588
[Oracle Database Reference 3]: https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_3192.htm#REFRN23587
[Creating a Flashback Data Archive]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFBJCCF
[Altering a Flashback Data Archive]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFIEEII
[Dropping a Flashback Data Archive]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFBCABH
[Specifying the Default Flashback Data Archive]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFCDJAD
[Enabling and Disabling Flashback Data Archive]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFEIJEE
[DDL Statements on Tables Enabled for Flashback Data Archive]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFJHDAG
[Viewing Flashback Data Archive Data]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFCAFDG
[Flashback Data Archive Scenarios]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFJCEGD
[Oracle Database SQL Language Reference 3]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5.htm#SQLRF012
[Oracle Database SQL Language Reference 4]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_1008.htm#SQLRF20009
[Oracle Database SQL Language Reference 5]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8015.htm#SQLRF20010
[Oracle Database SQL Language Reference 6]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_1004.htm#SQLRF00802
[Oracle Database SQL Language Reference 7]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#SQLRF30014
[Oracle Database SQL Language Reference 8]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#SQLRF01001
[Oracle Database SQL Language Reference 9]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10007.htm#SQLRF01707
[Oracle Database SQL Language Reference 10]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9019.htm#SQLRF01608
[Oracle Database SQL Language Reference 11]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9003.htm#SQLRF01806
[Oracle Database PL_SQL Packages and Types Reference 1]: https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_flashb.htm#ARPLS66402
[Table 12-3]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFJFFJD
[Oracle Database Reference 4]: https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5310.htm#REFRN26719
[Oracle Database Reference 5]: https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_3191.htm#REFRN23720
[Oracle Database Reference 6]: https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5311.htm#REFRN26715
[Scenario_ Using Flashback Data Archive to Enforce Digital Shredding]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFEDBHJ
[Scenario_ Using Flashback Data Archive to Access Historical Data]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFCJBAC
[Scenario_ Using Flashback Data Archive to Generate Reports]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFDJJJJ
[Scenario_ Using Flashback Data Archive for Auditing]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFDEBFC
[Scenario_ Using Flashback Data Archive to Recover Data]: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#BJFIEJGG