（以下这段话摘自oracle文档）

The version query pseudocolumns are valid only in Oracle Flashback Version Query, which is a form of Oracle Flashback Query. The version query pseudocolumns are:

· VERSIONS_STARTTIME: Returns the timestamp of the first version of the rows returned by the query.

· VERSIONS_STARTSCN: Returns the SCN of the first version of the rows returned by the query.

· VERSIONS_ENDTIME: Returns the timestamp of the last version of the rows returned by the query.

· VERSIONS_ENDSCN: Returns the SCN of the last version of the rows returned by the query.

· VERSIONS_XID: For each version of each row, returns the transaction ID (a RAW number) of the transaction that created that row version.

· VERSIONS_OPERATION: For each version of each row, returns a single character representing the operation that caused that row version. The values returned are I (for an insert operation), U (for an update operation) or D (for a delete operation).

flashback_query_clause

Use the flashback_query_clause to retrieve past data from a table, view, or materialized view.

This clause implements SQL-driven Flashback, which lets you specify a different system change number or timestamp for each object in the select list. You can also implement session-level Flashback using the DBMS_FLASHBACK package.

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

AS OF Specify AS OF to retrieve the single version of the rows returned by the query at a particular change number (SCN) or timestamp. If you specify SCN, then expr must evaluate to a number. If you specify TIMESTAMP, then expr must evaluate to a timestamp value. Oracle Database returns rows as they existed at the specified system change number or time.

VERSIONS Specify VERSIONS to retrieve multiple versions of the rows returned by the query. Oracle Database returns all committed versions of the rows that existed between two SCNs or between two timestamp values. The rows returned include deleted and subsequently reinserted versions of the rows.

· Specify BETWEEN SCN ... to retrieve the versions of the row that existed between two SCNs. Both expressions must evaluate to a number. MINVALUE and MAXVALUE resolve to the SCN of the oldest and most recent data available, respectively.

· Specify BETWEEN TIMESTAMP ... to retrieve the versions of the row that existed between two timestamps. Both expressions must evaluate to a timestamp value. MINVALUE and MAXVALUE resolve to the timestamp of the oldest and most recent data available, respectively.

Oracle Database provides a group of version query pseudocolumns that let you retrieve additional information about the various row versions. Please refer to "Version Query Pseudocolumns" for more information.

When both clauses are used together, the AS OF clause determines the SCN or moment in time from which the database issues the query. The VERSIONS clause determines the versions of the rows as seen from the AS OF point. The database returns null for a row version if the transaction started before the first BETWEEN value or ended after the AS OF point.

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

· You cannot specify a subquery in the expression of the AS OF clause.

· 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 this clause if you have specified query_name in the query_table_expression.

测试：

1 select * from t

2 versions between timestamp minvalue and maxvalue

3* where rownum<11

SQL> /

USERNAME USER_ID CREATED

------------------------------ ---------- --------------

TEST 12 25-4月 -07

MGMT_VIEW 12 25-4月 -07

SYSMAN 12 25-4月 -07

ANONYMOUS 12 25-4月 -07

XDB 12 25-4月 -07

MDSYS 12 25-4月 -07

SI_INFORMTN_SCHEMA 12 25-4月 -07

ORDPLUGINS 12 25-4月 -07

ORDSYS 12 25-4月 -07

EXFSYS 12 25-4月 -07

SQL> select username,user_id,created,decode(versions_operation,'I','insert','U','update','D','delete') operation,

2 versions_xid "xid",versions_starttime,versions_endtime from t

3 versions between timestamp minvalue and maxvalue

4 where rownum<11;

USERNAME USER_ID CREATED OPERAT xid VERSIONS_STARTTIME

------------------------------ ---------- -------------- ------ ---------------- --------------------------------------

TEST 12 25-4月 -07

MGMT_VIEW 12 25-4月 -07

SYSMAN 12 25-4月 -07

ANONYMOUS 12 25-4月 -07

XDB 12 25-4月 -07

MDSYS 12 25-4月 -07

SI_INFORMTN_SCHEMA 12 25-4月 -07

ORDPLUGINS 12 25-4月 -07

ORDSYS 12 25-4月 -07

EXFSYS 12 25-4月 -07

已选择10行。

SQL> update t

2 set username='sss';

已更新17行。

更新提交前事务回闪查询：

SQL> select username,user_id,created,decode(versions_operation,'I','insert','U','update','D','delete') operation,

2 versions_xid "xid",versions_starttime,versions_endtime from t

3 versions between timestamp minvalue and maxvalue

4 where rownum<11;

USERNAME USER_ID CREATED OPERAT xid VERSIONS_STARTTIME

------------------------------ ---------- -------------- ------ ---------------- -----------------------------------

TEST 12 25-4月 -07

MGMT_VIEW 12 25-4月 -07

SYSMAN 12 25-4月 -07

ANONYMOUS 12 25-4月 -07

XDB 12 25-4月 -07

MDSYS 12 25-4月 -07

SI_INFORMTN_SCHEMA 12 25-4月 -07

ORDPLUGINS 12 25-4月 -07

ORDSYS 12 25-4月 -07

EXFSYS 12 25-4月 -07

已选择10行。

SQL> commit;

提交完成。

更新提交后事务回闪查询：（只有事务提交了，才可以查到）

SQL> select username,user_id,created,decode(versions_operation,'I','insert','U','update','D','delete') operation,

2 versions_xid "xid",versions_starttime,versions_endtime from t

3 versions between timestamp minvalue and maxvalue

4 where rownum<11;

USERNAME USER_ID CREATED OPERAT xid VERSIONS_STARTTIME

------------------------------ ---------- -------------- ------ ---------------- ------------------------------------

sss 12 25-4月 -07 update 04001F0009010000 01-5月 -07 04.00.16 下午

已选择10行。

查询flashback_transaction_query:

SQL> desc flashback_transaction_query

名称

-------------------------------------------------------------------------------------------

XID

START_SCN

START_TIMESTAMP

COMMIT_SCN

COMMIT_TIMESTAMP

LOGON_USER

UNDO_CHANGE#

OPERATION

TABLE_NAME

TABLE_OWNER

ROW_ID

UNDO_SQL

SQL> select * from flashback_transaction_query

2 where XID='04001F0009010000';

XID START_SCN START_TIMESTAM COMMIT_SCN COMMIT_TIMESTA LOGON_USER

---------------- ---------- -------------- ---------- -------------- -----------------------

UNDO_SQL

--------------------------------------------------------------------------------------------