Saturday, March 15, 2014

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 Version Query
  • Using Oracle Flashback Transaction Query
  • Using Oracle Flashback Transaction Query with Oracle Flashback Version Query
  • Using ORA_ROWSCN
  • Using DBMS_FLASHBACK Package
  • Using Flashback Transaction
  • 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 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 do the following:
  • 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 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 executes 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 the following actions:
  • Roll back active transactions
  • Recover terminated transactions by using database or process recovery
  • Provide read consistency for SQL queries
Topics:
For additional general information about flashback features, see Oracle Database Concepts

Application Development Features

In application development, you can use the following 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 a time in the past that you specify with the AS OF clause of the SELECT statement. For more information, see 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 a Oracle Flashback Version Query, use the VERSIONS BETWEEN clause of the SELECT statement. For more information, see Using Oracle Flashback Version Query.
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 a Oracle Flashback Transaction Query, select from the static data dictionary view FLASHBACK_TRANSACTION_QUERY. For more information, see Using Oracle Flashback Transaction Query.
Typically, you use Oracle Flashback Transaction Query in conjunction with a 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).
DBMS_FLASHBACK Package
Use this feature to set the internal Oracle Database clock to a time in the past 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.
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 execute 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.
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).

Database Administration Features

The following 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 on . For more information about the database administration features, see Oracle Database Administrator's Guide and the 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 the following topics:

Configuring Your Database for Automatic Undo Management

To configure your database for Automatic Undo Management (AUM), you or your database administrator must do the following:
  • 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. Set the following 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 as well as 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.
    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 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 do the following:
  • 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 do the following:
  • 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 already, enable supplemental logging:
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

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 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 the following flashback features. For information about the GRANT statement, see Oracle Database SQL Language Reference.
For Oracle Flashback Query and Oracle Flashback Version Query
Do either of the following:
  • 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 a 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 the following 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 some time in the past. The query explicitly references a past time through a timestamp 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 some time in the past.
    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:
For more information about the SELECT AS OF statement, see Oracle Database SQL Language Reference.

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 13-1 retrieves the state of the record for Chung at 9:30AM, April 4, 2004:
Example 13-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 13-2 restores Chung's information to the employees table:
Example 13-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.
  • You can use the AS OF clause in queries to perform DDL operations (such as creating and truncating tables) or DML operations (such as inserting and deleting) 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 timestamp. See 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, the following query reinserts into table employees the rows that existed an hour ago:
    INSERT INTO employees
    (SELECT * FROM employees
     AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)
    )
    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 new 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 13-1. This information can reveal when and how a particular change (perhaps erroneous) occurred to your database.
Table 13-1 Oracle Flashback Version Query Row Data Pseudocolumns
Pseudocolumn Name Description
VERSIONS_STARTSCN
VERSIONS_STARTTIME		 Starting System Change Number (SCN) or TIMESTAMP 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.
If this pseudocolumn is NULL, then the row version was created before start.
VERSIONS_ENDSCN
VERSIONS_ENDTIME		 SCN or TIMESTAMP when the row version expired.
If this pseudocolumn is NULL, then either the row version was current at the time of the query or the row corresponds to a DELETE operation.
VERSIONS_XID Identifier of the transaction that created the row version.
VERSIONS_OPERATION Operation performed by the transaction: I for insertion, D for deletion, or U for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an INSERT operation, the row before a DELETE operation, or the row affected by an UPDATE operation.
For user updates of an index key, Oracle Flashback Version Query might treat an UPDATE operation as two operations, DELETE plus INSERT, represented as two version rows with a Dfollowed by an I VERSIONS_OPERATION.
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, the following 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,
       name, salary
  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')
  WHERE name = 'JOE';
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.
See Also:
Oracle Database SQL Language Reference 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.
The column UNDO_SQL shows the SQL code that is the 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.
The following 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');
The following 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')
    );
See Also:

Using Oracle Flashback Transaction Query with Oracle Flashback Version Query

This example uses simple versions of the employees and departments tables in the sample HR schema.
In this example, a database administrator uses SQL*Plus to do the following:
CREATE TABLE emp 
   (empno   NUMBER PRIMARY KEY,
    empname VARCHAR2(16)
    salary  NUMBER);
INSERT INTO emp VALUES (111, 'Mike', 555);
COMMIT;

CREATE TABLE dept
   (deptno   NUMBER,
    deptname VARCHAR2(32));
INSERT INTO dept VALUES (10, 'Accounting');
COMMIT;
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 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 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 the following 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 hr.emp
  VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
  where empno = 111;

XID              START_SCN  END_SCN   OPERATION  EMPNAME    SALARY
---------------- ---------- --------- ---------- ---------- ----------
0004000700000058 113855               I          Tom        927
000200030000002D 113564               D          Mike       555
000200030000002E 112670     113564    I          Mike       555
3 rows selected
The results table 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 000200030000002D as the erroneous transaction and uses Oracle Flashback Transaction Query to audit all changes made by this transaction:
SELECT  xid, start_scn START, commit_scn COMMIT, operation OP,
  logon_user USER, undo_sql
    FROM flashback_transaction_query
      WHERE xid = HEXTORAW('000200030000002D');

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

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

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

000200030000002D  195243  113565  BEGIN  HR

4 rows selected
To see the details of the erroneous transaction and all subsequent transactions, the database administrator performs the following 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');

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

6 rows selected

Using ORA_ROWSCN

ORA_ROWSCN is a pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row; that is, the latest COMMIT operation for the row. For example:
SELECT ora_rowscn, last_name, salary
  FROM employees
  WHERE employee_id = 7788;

ORA_ROWSCN    NAME    SALARY
----------    ----    ------
    202553    Fudd      3000
The latest COMMIT operation for the row took place at approximately SCN 202553. To convert an SCN to the corresponding TIMESTAMP value, use the function SCN_TO_TIMESTAMP.
ORA_ROWSCN is a conservative upper bound of the latest commit time—the actual commit SCN can be somewhat earlier. ORA_ROWSCN is more precise (closer to the actual commit SCN) for a row-dependent table (created using CREATE TABLE with the ROWDEPENDENCIES clause).
Uses of ORA_ROWSCN in application development include concurrency control and client cache invalidation.
Topics:

Scenario: Concurrency Control

Your application examines a row of data and records the corresponding ORA_ROWSCN as 202553. Later, the application must update the row, but only if the row has not changed. The operation is made conditional on the ORA_ROWSCN being still 202553. An equivalent interactive statement is:
UPDATE employees
  SET salary = salary + 100
  WHERE employee_id = 7788
  AND ora_rowscn = 202553;

0 rows updated.
The conditional update fails in this case, because the ORA_ROWSCN is no longer 202553. Therefore, a user or another application changed the row and performed a COMMIT more recently than the recorded ORA_ROWSCN.
Your application queries again to obtain the new row data and ORA_ROWSCN. Suppose that the ORA_ROWSCN is now 415639. The application tries the conditional update again, using the new ORA_ROWSCN. This time, the update succeeds, and it is committed. An interactive equivalent is:
SQL> UPDATE employees SET salary = salary + 100
     WHERE empno = 7788 AND ora_rowscn = 415639;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT ora_rowscn, name, salary FROM employees WHERE empno = 7788;

ORA_ROWSCN    NAME    SALARY
----------    ----    ------
    465461    Fudd      3100
The SCN corresponding to the new COMMIT is 465461.
Besides using ORA_ROWSCN in an UPDATE statement WHERE clause, you can use it in a DELETE statement WHERE clause or the AS OF clause of Oracle Flashback Query.

ORA_ROWSCN and Tables with Virtual Private Database (VPD)

When a VPD policy is added to a table, it is no longer possible to select the ORA_ROWSCN pseudocolumn. However, because ORA_ROWSCN is available inside the policy function, you can do the following:
  1. Create a function that returns a row SCN, as in Example 13-3.
  2. In the policy predicate function, add a predicate that will store the row SCN in the context that the function will use while processing rows. For example:
    ||' AND f_ora_rowscn('||object_name||'.ora_rowscn) = 1'
  3. Use the function to fetch the row. For example:
    SELECT t.*, get_rowscn(t.rowid) "ORA_ROWSCN" FROM test_table t;
Example 13-3 Function that Can Return Row SCN from Table that has VPD
-- Create context that function will use while processing rows:
 
CREATE OR REPLACE FUNCTION f_ora_rowscn
 (rowscn IN NUMBER)
 RETURN NUMBER
AS
BEGIN
  DBMS_SESSION.SET_CONTEXT('STORE_ROWSCN','ROWSCN',rowscn);
  RETURN 1;
END;
/
 
CREATE CONTEXT store_rowscn USING f_ora_rowscn;
 
-- Create function that returns row SCN for each row:
 
CREATE OR REPLACE FUNCTION get_rowscn
  (row IN ROWID)
  RETURN VARCHAR2
AS
BEGIN 
  RETURN sys_context('STORE_ROWSCN','ROWSCN');
END;
/

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, carry out normal queries as if you were at that time in the past, 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 times in the past.
You must have the EXECUTE privilege on the DBMS_FLASHBACKpackage.
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 time 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 do the following:
  • 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:

Using Flashback Transaction

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

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 13-2
For the syntax of the TRANSACTION_BACKOUT procedure and detailed parameter descriptions, see Oracle Database PL/SQL Packages and Types Reference.
Table 13-2 Flashback TRANSACTION_BACKOUT Options
Option Description
CASCADE Backs out specified transactions and all dependent transactions in a post-order fashion (that is, children are backed out before parents are backed out).
Without CASCADE, if any dependent transaction is not specified, an error occurs.
NOCASCADE Default. Backs out specified transactions, which are expected to have no dependent transactions. First dependent transactions causes an error and appears in *_FLASHBACK_TRANSACTION_REPORT.
NOCASCADE_FORCE Backs out specified transactions, ignoring dependent transactions. Server executes undo SQL statements for specified transactions in reverse order of commit times.
If no constraints break and you are satisfied with the result, you can commit the changes; otherwise, you can roll them back.
NONCONFLICT_ONLY Backs out changes to nonconflicting rows of the specified transactions. Database remains consistent, but transaction atomicity is lost.
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 with respect to 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 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 for more information about *_FLASHBACK_TXN_REPORT

Using Flashback Data Archive (Oracle Total Recall)

A Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. 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 the following are true:
  • You have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table.
  • The table is neither nested, clustered, temporary, remote, or external.
  • The table contains neither LONG nor nested columns.
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. While flashback archiving is enabled for a table, some DDL statements are not allowed on that table.
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:
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 the following:
  • 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, you will get error ORA-55621.
  • Retention time (number of days that Flashback Data Archive data for the table is guaranteed to be stored)
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).
Examples
  • Create a default Flashback Data Archive named fla1 that uses up to 10 G of tablespace tbs1, whose data will be 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 will be 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.

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.

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.

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 one in one of the following 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:

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 that you want 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 will be 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, you will get error ORA-55614 when you try to modify the table.
If a table already 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 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 Not Allowed on Tables Enabled for Flashback Data Archive

Using any of the following DDL statements on a table enabled for Flashback Data Archive causes error ORA-55610:
  • ALTER TABLE statement that does any of the following:
    • Drops, renames, or modifies a column
    • Performs partition or subpartition operations
    • Converts a LONG column to a LOB column
    • Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
  • DROP TABLE statement
  • RENAME TABLE statement
  • TRUNCATE TABLE statement
See Also:
Oracle Database SQL Language Reference for information about these DDL statements

Viewing Flashback Data Archive Data

Table 13-3 lists and briefly describes the static data dictionary views that you can query for information about Flashback Data Archive files.
Table 13-3 Static Data Dictionary Views for Flashback Data Archive Files
View Description
*_FLASHBACK_ARCHIVE Displays information about Flashback Data Archive files.
*_FLASHBACK_ARCHIVE_TS Displays tablespaces of Flashback Data Archive files.
*_FLASHBACK_ARCHIVE_TABLES Displays information about tables that are enabled for Data Flashback Archive files.
See Also:

Flashback Data Archive Scenarios

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 will be 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 the following 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 the following 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 will be 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 the following 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 will be 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 the following query:
SELECT date_billed, amount_billed, patient_name, claim_Id,
  test_costs, diagnosis FROM Billings AS OF
  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 timestamp from the transaction query corresponds to Bob's transaction, two days ago.
Next, Bob uses the following 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 timestamp 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, you will get error ORA-1466. 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 timestamp, 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 timestamps at a granularity of 3 seconds.
    For example, suppose that the SCN values 1000 and 1005 are mapped to the timestamps 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.
    Due to this time-to-SCN mapping, 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-1466.
  • 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.

Performance Guidelines for Oracle Flashback Technology

  • Use the DBMS_STATS package to generate statistics for all tables involved in a 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 already in the buffer cache. The performance cost in CPU use is the cost of applying undo information to affected data blocks. When operating on changes in the recent past, flashback operations are CPU-bound.
  • For Oracle Flashback Version Query, use index structures. Oracle Database keeps undo data for index changes as well as 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 a 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).
  • A Oracle Flashback Query against a materialized view does not take advantage of query rewrite optimization.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)