16 Using Oracle Flashback Technology

16.1 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. It is retained for the time specified by undo_retention, or up to the tuned undo retention in the presence of Automatic Undo Management (AUM). 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

16.2 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

16.3 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.

• Selecting data that was valid at a particular time or at any time within a user-defined valid time period.

  For example, you can find employees with valid employee information as of a particular timestamp or between a specified start and end time in the specified valid time period. (For more information, see Section 1.9.4, "Temporal Validity Support".)

• 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.

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

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

16.4 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 either:

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).

or:

VERSIONS PERIOD FOR user_valid_time [ BETWEEN TIMESTAMP start AND end ]

where user_valid_time refers to the user-specified valid time period, as explained in Section 1.9.4, "Temporal Validity Support."

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, which can reveal when and how a particular change (perhaps erroneous) occurred to your database.

Table 16-1 describes the pseudocolumns of metadata about the row version. The VERSIONS_* pseudocolumns have values only for transaction-time Flashback Version Queries (that is, queries with the clause BETWEEN TIMESTAMP start AND end).

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 Section 16.5, "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.

16.5 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.

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

16.6 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.

16.7 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 returns the current SCN regardless of previous invocations of DBMS_FLASHBACK.ENABLE.

16.8 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.

Topics:

• Dependent Transactions

• TRANSACTION_BACKOUT Parameters

• TRANSACTION_BACKOUT Reports

16.9 Using Flashback Data Archive

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 specified when the Flashback Data Archive was created.

By default, Flashback Data Archive is not enabled for any tables. You can enable Flashback Data Archive 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 not nested, 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

You cannot enable Flashback Data Archive on a nested table, temporary table, external table, materialized view, Advanced Query (AQ) table, or non-table object.

After Flashback Data Archive 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.

Effective with Oracle Database 12c Release 1 (12.1.0.1), Flashback Data Archive is enhanced to include the following:

• User context tracking. The metadata information for tracking transactions can include (if the feature is enabled) the user context, which makes it easier to determine which user made which changes to a table.

  To set the user context level (determining how much user context is to be saved), use the DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL procedure. To access the context information, use the DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT function. (The DBMS_FLASHBACK_ARCHIVE package is described in Oracle Database PL/SQL Packages and Types Reference.)

• Database hardening. This feature enables you to associate a set of tables together in an "application", and then enable Flashback Data Archive on all those tables with a single command. Also, database hardening enables you to lock all the tables with a single command, preventing any DML on those tables until they are subsequently unlocked. Database hardening is designed to make it easier to use Flashback Data Archive to track and protect the security-sensitive tables for an application.

  To register an application for database hardening, use the DBMS_FLASHBACK_ARCHIVE.REGISTER_APPLICATION procedure, which is described in Oracle Database PL/SQL Packages and Types Reference.

• Import and export of history using DBMS_FLASHBACK_ARCHIVE procedures to create a temporary history table, and then later importing that table into the designated history table after loading that table with the desired history data. The temporary history table can be loaded using a variety of methods, including Data Pump. Support is also included for importing user-generated history. If you have been maintaining history using some other mechanism, such as triggers, you can import that history into Flashback Data Archive. (The DBMS_FLASHBACK_ARCHIVE package is described in Oracle Database PL/SQL Packages and Types Reference.)

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

DROP FLASHBACK ARCHIVE fla1;

CREATE FLASHBACK ARCHIVE taxes_archive TABLESPACE tbs1 RETENTION 10 YEAR;

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

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

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

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;

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

ALTER TABLE investments FLASHBACK ARCHIVE;

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

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

ALTER TABLE Billings FLASHBACK ARCHIVE;

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

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

16.10 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, 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 immediately 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 returns current data.

• You can perform queries on past data in static data dictionary views, such as *_TABLES.

• 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.

16.11 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.

  Oracle recommends 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.

• If very large transactions (such as affecting more than 1 million rows) are performed on tracked tables, set the large pool size high enough (at least 1 GB) for Parallel Query not to have to allocate new chunks out of the SGA. (For information about setting the large pool size, see the chapter about tuning the shared pool and the large pool in Oracle Database Performance Tuning Guide.)

• 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.

16.12 Multitenant Container Database Restrictions for Oracle Flashback Technology

These Oracle Flashback Technology features are unavailable for a multitenant container database (CDB):

• For Oracle Database 12c Release 1 (12.1.0.1), Flashback Data Archive (FDA) is not supported in a CDB.

  For Oracle Database 12c Release 1 (12.1.0.2), this restriction is removed.

• Flashback Transaction Query is not supported in a CDB.

• Flashback Transaction Backout is not supported in a CDB.