加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 (https://www.lidatong.com.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 百科 > 正文

Flashback Transaction Query

发布时间:2020-12-15 06:09:51 所属栏目:百科 来源:网络整理
导读: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 a COMMIT statement is executed. You specify a Flashback Version Query us

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

(编辑:李大同)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读