Oracle Flashback Transaction Query

flashback transaction query是一种诊断工具,帮助识别数据库发生的事务级变化,可以用于事务审计的数据分析,它是基于undodata,利用undo_retention参数来确定已经提交的撤销数据在数据库中的保存时间。

之前的一个flashback version query例子可以实现审计一段时间内表的所有改变,但对错误的事务不能进行撤销处理。而flashback transaction query可以实现撤销处理,因为可以从flashback_transaction_query视图中获得事务的历史操作及undo_sql也就是撤销语句,也就是说我们可以审计一个事务做了什么,也可以撤销一个已经提交的事务。

以ZWC用户的T_USER表为例
查看flashback_transaction_query视图结构

21:27:14 SQL> SHOW USER
USER IS "ZWC"
21:28:19 SQL> DESC flashback_transaction_query
 Name                       NULL?    TYPE
 -------------------------- -------- ------------------
 XID                                 RAW(8)
 START_SCN                           NUMBER
 START_TIMESTAMP                     DATE
 COMMIT_SCN                          NUMBER
 COMMIT_TIMESTAMP                    DATE
 LOGON_USER                          VARCHAR2(30)
 UNDO_CHANGE#                        NUMBER
 OPERATION                           VARCHAR2(32)
 TABLE_NAME                          VARCHAR2(256)
 TABLE_OWNER                         VARCHAR2(32)
 ROW_ID                              VARCHAR2(19)
 UNDO_SQL                            VARCHAR2(4000)

XID                  事务标识
START_SCN            事务起始SCN
START_TIMESTAMP      事务起始时间戳
COMMIT_SCN           事务提交SCN
COMMIT_TIMESTAMP     事务提交时间戳
LOGON_USER           登陆用户
UNDO_CHANGE#         撤销改变号
OPERATION            操作类型
TABLE_NAME           表名
TABLE_OWNER          表拥有者
ROW_ID               行标识
UNDO_SQL             撤销的SQL

删除一条记录,并且查看XID

21:34:34 SQL> DELETE FROM t_user WHERE userid = '001';

1 ROW deleted.

21:34:48 SQL> commit;

Commit complete.

21:36:36 SQL> SELECT username,userid,versions_xid,to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') FROM t_user 
21:37:56   2  versions BETWEEN TIMESTAMP minvalue AND maxvalue ORDER BY versions_starttime DESC;

USERNAME   USERID     VERSIONS_XID     TO_CHAR(VERSIONS_ST
---------- ---------- ---------------- -------------------
jarry      001        05002100E4020000 2012-01-11 21:34:50
tom        002        0800180097030000 2012-01-11 21:16:32
jarry      001        0800180097030000 2012-01-11 21:16:32

找出XID为 05002100E4020000 的UNDO_SQL

21:39:16 SQL> col TABLE_NAME format a18
21:39:38 SQL> col table_owner format a11
21:39:46 SQL> col logon_user format a12
21:40:01 SQL> SET long 1000;
21:40:09 SQL> SELECT logon_user,TABLE_NAME,table_owner,undo_sql FROM 
21:40:38   2  flashback_transaction_query WHERE table_owner='ZWC' AND xid='05002100E4020000';

LOGON_USER   TABLE_NAME         TABLE_OWNER
------------ ------------------ -----------
UNDO_SQL
----------------------------------------------------------------------------------------------------
ZWC          T_USER             ZWC
INSERT INTO "ZWC"."T_USER"("USERID","USERNAME","REGDATE","LASTWRITETIME","EMAIL","TEL","PASSWORD") v
alues ('001','jarry',TO_DATE('10-JAN-12', 'DD-MON-RR'),TO_DATE('12-JAN-12', 'DD-MON-RR'),'jarry@sina
.com','13814010000','JarpwD');

找到了UNDO_SQL接下来我们可以撤销XID为05002100E4020000 的事务,直接执行UNDO_SQL然后COMMIT就可以

21:44:26 SQL> SELECT * FROM t_user;

USERID     USERNAME   REGDATE    LASTWRITETIME        EMAIL                TEL         PASSWORD
---------- ---------- ---------- -------------------- -------------------- ----------- ----------
002        tom        10-JAN-12  14-JAN-12            tom@126.com          13440099509 ToMpWD

21:44:30 SQL> INSERT INTO "ZWC"."T_USER"("USERID","USERNAME","REGDATE","LASTWRITETIME","EMAIL","TEL","PASSWORD") 
21:44:43   2  VALUES
21:44:44   3  ('001','jarry',TO_DATE('10-JAN-12', 'DD-MON-RR'),TO_DATE('12-JAN-12', 'DD-MON-RR'),'jarry@sina.com','13814010000','JarpwD');

1 ROW created.

21:45:07 SQL> commit;

Commit complete.

21:45:10 SQL> SELECT * FROM t_user;

USERID     USERNAME   REGDATE    LASTWRITETIME        EMAIL                TEL         PASSWORD
---------- ---------- ---------- -------------------- -------------------- ----------- ----------
001        jarry      10-JAN-12  12-JAN-12            jarry@sina.com       13814010000 JarpwD
002        tom        10-JAN-12  14-JAN-12            tom@126.com          13440099509 ToMpWD