Oracle Database - Flashback Query

Card Puncher Data Processing

About

In a Flashback Query operation (SELECT … AS OF), the SELECT statement explicitly specifies the point in time. For example, you can query a table as it appeared last Thursday at 2 p.m.

Limitations

When performing a flashback query, Oracle Database might not use query optimizations that it would use for other types of queries, which could have a negative impact on performance. In particular, this occurs when you specify multiple flashback queries in a hierarchical query.

Restrictions on Flashback Queries These queries are subject to the following restrictions:

  • You cannot specify a column expression or a subquery in the expression of the AS OF clause.
  • You cannot specify the AS OF clause if you have specified the for_update_clause.
  • You cannot use the AS OF clause in the defining query of a materialized view.
  • You cannot use the VERSIONS clause in flashback queries to temporary or external tables, or tables that are part of a cluster.
  • You cannot use the VERSIONS clause in flashback queries to views. However, you can use the VERSIONS syntax in the defining query of a view.
  • You cannot specify the flashback_query_clause if you have specified query_name in the query_table_expression.

The database does not use dynamic statistics for queries that contain the AS OF clause.

Privileges

To issue an Flashback Query using the flashback_query_clause, you must have the SELECT privilege on the objects in the select list. In addition, either you must have FLASHBACK object privilege on the objects in the select list, or you must have FLASHBACK ANY TABLE system privilege.

Privileges needed for the FLASHBACK_TRANSACTION_QUERY:

GRANT SELECT ANY TRANSACTION TO user;
grant SELECT on SYS.FLASHBACK_TRANSACTION_QUERY to user;

Pseudo-columns

Version Query Pseudocolumns

A Flashback Query lets you retrieve a history of changes made to a row. You can retrieve the corresponding identifier of the transaction that made the change using the VERSIONS_XID pseudocolumn.

Data dictionary

You can also retrieve information about the transaction that resulted in a particular row version by issuing an Oracle Flashback Transaction Query. You do this by querying the FLASHBACK_TRANSACTION_QUERY data dictionary view for a particular transaction ID.

Support

ORA-01031 - insufficient privileges

See privileges or What are the SELECT ANY TRANSACTION / FLASHBACK ANY TABLE Privileges ?

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - Read Consistency

in Oracle The database uses a transaction table, also called an interested transaction list (ITL), to determine if a transaction was uncommitted when the database began modifying the block. The block...
Card Puncher Data Processing
Oracle Database 12c - Temporal Validity

Temporal validity permits to retrieve the data as it was in a point in time by defining a validity period for which a given record is considered valid. This time-based validity is determined by the start...
Data System Architecture
Transactions - Rollback Journal (Undo journal)

A rollback journal consists of records of the actions of transactions, primarily before they are committed. Its name comes from the fact that its primary function is to roll back (undo) changes from...



Share this page:
Follow us:
Task Runner