ITPub博客

首页 > Linux操作系统 > Linux操作系统 > flashback transaction query

flashback transaction query

原创 Linux操作系统 作者:fengjin821 时间:2009-06-08 16:46:40 0 删除 编辑
flashback transaction query

flashback transaction query其实是flashback version query查询的一个扩充,flashback version query我们说明了我们可以审计一段时间内表的所有改变,但是也仅仅是能发现问题,对于错误的事务,没有好的处理办法。但是flashback transaction query提供了我们办法,我们可以从FLASHBACK_TRANSACTION_QUERY中获得事务的历史以及Undo_sql,也就是说,我们审计一个事务到底做了什么甚至可以回滚一个已经提交的事务。

flashback transaction query需要用到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  
其定义为:
select xid, start_scn, start_timestamp,
          decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
          commit_scn, commit_timestamp,
          logon_user, undo_change#, operation, table_name, table_owner,
          row_id, undo_sql
from sys.x$ktuqqry

好,现在,我们试着回滚一个已经提交的事务。
其实,需要从FLASHBACK_TRANSACTION_QUERY获得数据,关键问题是获得事务XID,
SQL> create table test as select * from all_objects;
Table created.

SQL> set time on
11:15:48 SQL> delete from test where rownum <=10;
10 rows deleted.

11:16:10 SQL> select xid from v$transaction;
XID
----------------
04001200AE010000

11:20:09 SQL> commit;
Commit complete.

当然,我们在测试中,可以在事务没有提交的时候,获得事务的04001200AE010000。
实际情况下,我们不可能去跟踪每个事务,想要获得已提交事务的XID,就必须通过flashback version query了,如

11:30:32 SQL> select versions_xid, versions_operation
11:30:41   2    from test versions between timestamp
11:30:41   3    to_date('2004-04-08 11:15:48','yyyy-mm-dd hh24:mi:ss')
11:30:41   4    and MAXVALUE
11:30:41   5    WHERE versions_xid is not null
11:30:41   6    order by VERSIONS_STARTTIME;

VERSIONS_XID     V
---------------- -
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
10 rows selected.

可以看到,我们共删除了10行,对应的是10个操作。这10个操作都是同一个事务,我们现在利用这个XID来获得UNDO_SQL。

SQL>SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '04001200AE010000';

UNDO_SQL
------------------------------------------------------------------------------
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA

这个删除语句对应的是10个insert语句,如果我们想回滚这个事务,我们执行这10个insert语句即可(以上语句都是完整的语句,这里因为显示问题,我就不完全显示了)。

可以看到,flashback transaction query主要用于审计一个事务,并可以回滚一个已经提交的事务。如果确定出错的事务是最后一个事务,我们利用falshback table或者flashback query就可以解决问题,但是,如果我们执行了一个错误的事务之后,又执行了一系列正确的事务,那么。利用flashback transaction query,我们甚至可以是回滚这个错误的事务。

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

请登录后发表评论 登录
全部评论

注册时间:2009-04-29

  • 博文量
    191
  • 访问量
    511706