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 |
|---|---|
|
Starting System Change Number (SCN) or If this is |
|
|
SCN or If this is |
|
|
Identifier of the transaction that created the row version. |
|
|
Operation performed by the transaction: Note: For user updates of an index key, a Flashback Version Query may treat an |
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