Table of Contents

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