flashback系列文章四（flashback version/transaction query）

一、flashback version query

SQL> create table fb_version_test(id number,name varchar2(50));

Table created.

对fb_version_test做一些dml操作

SQL> insert into fb_version_test values(1,'thomas');

1 row created.

SQL> commit;

Commit complete.

SQL>  insert into fb_version_test values(2,'jack');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into fb_version_test values(3,'rose');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into fb_version_test values(4,'harward');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into fb_version_test values(5,'obma');

1 row created.

SQL> commit;

Commit complete.

SQL> update fb_version_test set id=10 where name='thomas';

1 row updated.

SQL> commit;

Commit complete.

SQL> update fb_version_test set id=100 where name='thomas';

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from fb_version_test where id=5;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select id,name,versions_starttime,versions_endtime,versions_xid,versions_operation
  2   from fb_version_test
  3   versions between timestamp minvalue and maxvalue
  4  order by versions_starttime;

        ID NAME                                               VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     V
---------- -------------------------------------------------- ------------------------- ------------------------- ---------------- -
         1 thomas                                             31-DEC-13 11.34.45 PM     31-DEC-13 11.36.48 PM     0A001A0021010000 I
         2 jack                                               31-DEC-13 11.35.03 PM                               0400220022010000 I
         3 rose                                               31-DEC-13 11.35.24 PM                               05002C006B010000 I
         4 harward                                            31-DEC-13 11.35.54 PM                               090004007E010000 I
         5 obma                                               31-DEC-13 11.36.06 PM     31-DEC-13 11.37.48 PM     010006001E010000 I
        10 thomas                                             31-DEC-13 11.36.48 PM     31-DEC-13 11.37.06 PM     04002A0022010000 U
       100 thomas                                             31-DEC-13 11.37.06 PM                               050020006B010000 U
         5 obma                                               31-DEC-13 11.37.48 PM                               030004005D010000 D

8 rows selected.
这样对表的dml操作一目了然。
语法：

SELECT .....FROM tablename VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end} 

Flashback Version Query伪列说明

versions_start{scn|time}  版本开始的scn或时间戳

 

versions_end{scn|time}  版本结束scn或时间戳，如果有值表明此行后面被更改过是旧版本，如果为null，则说明行版本是当前版本或行被删除（即versions_operation值为D）。

 

versions_xid 创建行版本的事务ID

 

versions_operation  在行上执行的操作（I=插入，D=删除，U=更新）

二、flashback transation query

SQL> conn sys/oracle as sysdba
Connected.
SQL> select owner,view_name from dba_views where view_name='FLASHBACK_TRANSACTION_QUERY';

OWNER                          VIEW_NAME
------------------------------ ------------------------------
SYS                            FLASHBACK_TRANSACTION_QUERY
SQL> desc flashback_transaction_query;

Name             Type           Nullable Default Comments               

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

XID              RAW(8)         Y        Transaction identifier   

START_SCN        NUMBER         Y        Transaction start SCN   

START_TIMESTAMP  DATE           Y        Transaction start timestamp   

COMMIT_SCN       NUMBER         Y        Transaction commit SCN  

COMMIT_TIMESTAMP DATE           Y        Transaction commit timestamp  

LOGON_USER       VARCHAR2(30)   Y        Logon user for transaction   

UNDO_CHANGE#     NUMBER         Y        1-based undo change number  

OPERATION        VARCHAR2(32)   Y        forward operation for this undo  

TABLE_NAME       VARCHAR2(256)  Y        table name to which this undo applies     

TABLE_OWNER      VARCHAR2(32)   Y        owner of table to which this undo applies

ROW_ID           VARCHAR2(19)   Y        rowid to which this undo applies         

UNDO_SQL         VARCHAR2(4000) Y        SQL corresponding to this undo  

假设我们要把上面update fb_version_test set id=100 where name='thomas'语句进行回滚，从上面flashback version query查到xid为050020006B010000

SQL> select undo_sql from flashback_transaction_query
  2   where xid=hextoraw('050020006B010000');

UNDO_SQL
----------------------------------------------------------------------------------------------------
update "SCOTT"."FB_VERSION_TEST" set "ID" = '10' where ROWID = 'AAAMmfAAEAAAACHAAA';

我们只要拿出这个undo_sql在数据库中执行下就可以回滚该事务了
事实上，如果一个事务进行了非常多的操作才做的提交，那么通过flashback transation query查找到undo_sql进行该事务的撤销将会非常的有用和方便

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26613085/viewspace-1066124/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26613085/viewspace-1066124/