Flashback Transaction Query

You use a 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 aCOMMIT statement is executed.

You specify a Flashback Version Query using the VERSIONS BETWEEN clause of theSELECT statement. Here is the syntax:

VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}

Table 10-1 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 identifies the time when the data first took on the values reflected in the row version. You can use this to identify the past target time for a Flashback Table or Flashback Query operation. If this is NULL, then the row version was created before the lower time bound of the queryBETWEEN clause.
VERSIONS_ENDSCN VERSIONS_ENDTIME	SCN or TIMESTAMP when the row version expired. This identifies the row expiration time. If this is NULL, then either the row version was still current at the time of the query or the row corresponds to aDELETE 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, orU for update. The version is that of the row that was inserted, deleted, or updated; that is, the rowafter anINSERT operation, the row before a DELETE operation, or the row affected by an UPDATE operation. Note: For user updates of an index key, a Flashback Version Query may treat anUPDATE operation as two operations,DELETE plus INSERT, represented as two version rows with aD followed by anI VERSIONS_OPERATION.

 

 

Here is a typical 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';

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
     FROM flashback_transaction_query
     WHERE xid = HEXTORAW('000200030000002D');

 

 

 

举例：

SQL> create table t1 as  select *from dept;

表已创建。

SQL> insert into  t1 select * from dept where deptno=10;

已创建 1 行。

SQL> update  t1 set loc='a' where deptno=10;

已更新2行。

SQL> update  t1 set loc='b' where deptno=10;

已更新2行。

SQL> commit;

提交完成。

SQL> select  versions_xid, versions_operation,t1.*  from scott.t1 VERSIONS BETWEEN SCN MINVALUE AND M
AXVALUE where dname='ACCOUNTING' ;

VERSIONS_XID     VE     DEPTNO DNAME                        LOC
---------------- -- ---------- ---------------------------- --------------------------
04002300BF050000 U          10 ACCOUNTING                   b
                                               10 ACCOUNTING                   NEW YORK
04002300BF050000 I          10 ACCOUNTING                   b

SQL> select  versions_xid, versions_operation,t1.*  from scott.t1 VERSIONS BETWEEN SCN MINVALUE AND M
AXVALUE where dname='ACCOUNTING' ;

VERSIONS_XID     VE     DEPTNO DNAME                        LOC
---------------- -- ---------- ---------------------------- --------------------------
04002300BF050000 U          10 ACCOUNTING                   b
                                                 10 ACCOUNTING                   NEW YORK
04002300BF050000 I          10 ACCOUNTING                   b

 

 

举例二：

 

SQL> drop table t1;

表已删除。

SQL> create table t1  as  select * from dept;

表已创建。

SQL> update t1 set deptno=1 where dname='ACCOUNTING';

已更新 1 行。

SQL> update t1 set deptno=2 where dname='ACCOUNTING';

已更新 1 行。

SQL> update t1 set deptno=3 where dname='ACCOUNTING';

已更新 1 行。

SQL> alter table t1 drop column loc;

表已更改。

SQL> commit;

提交完成。

SQL> update t1 set deptno=4 where dname='ACCOUNTING';

已更新 1 行。

SQL> update t1 set deptno=5 where dname='ACCOUNTING';

已更新 1 行。

SQL> update t1 set deptno=6 where dname='ACCOUNTING';

已更新 1 行。

SQL> commit;

提交完成。

SQL> select  versions_xid, versions_operation,t1.*  from
AXVALUE where dname='ACCOUNTING' ;

VERSIONS_XID     VE     DEPTNO DNAME
---------------- -- ---------- --------------------------
0400020094050000 U           6 ACCOUNTING
                                                    3 ACCOUNTING
 

通过此例子告诉我们Flashback Transaction Query 只能是在commit以后的数据，只能是DML语句，ddl语句不行，DDL以后前面的DML语句查不到，不用开启row movement