【Flashback】Flashback Transaction Query功能实践

ITAA-OCM

1.Flashback Transaction Query功能
从FLASHBACK_TRANSACTION_QUERY视图中获得事务的历史以及Undo_sql，也就是说可以通过查询视图FLASHBACK_TRANSACTION_QUERY获得表的操作记录，同时可以获得恢复错误操作的SQL语句。

2.体验一下这个功能带给我们的神奇吧
1）创建测试表test_ftq，并进行一些简单的插入和删除操作
sec@orcl> create table test_ftq (a int, b int);

Table created.

sec@orcl> insert into test_ftq values (1,1);

1 row created.

sec@orcl> insert into test_ftq values (2,2);

1 row created.

sec@orcl> insert into test_ftq values (3,3);

1 row created.

sec@orcl> commit;

Commit complete.

2）查询在一个时间段内对表test_ftq的操作记录
sec@orcl> select versions_xid, versions_operation
  2  from test_ftq
  3 versions between timestamp to_date('2009-04-28 05:28:06','yyyy-mm-dd hh24:mi:ss') and maxvalue
  4  WHERE versions_xid is not null
  5  order by versions_starttime;

VERSIONS_XID     V
---------------- -
04001E0043950400 I
04001E0043950400 I
04001E0043950400 I
04001A0042950400 D

3）根据VERSIONS_XID的信息可以通过查询视图FLASHBACK_TRANSACTION_QUERY得到相应的回滚SQL语句
sec@orcl> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where xid='04001E0043950400';

UNDO_SQL
------------------------------------------------------------------------------------------------------------------------------
delete from "SEC"."TEST_FTQ" where ROWID = 'AABIejAAfAAAAASAAA';


sec@orcl> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where xid='04001A0042950400';

UNDO_SQL
------------------------------------------------------------------------------------------------------------------------------
insert into "SEC"."TEST_FTQ"("A","B") values ('1','1');

3.之所以有这样神奇的效果，与视图FLASHBACK_TRANSACTION_QUERY是分不开的，OK，让我们看看oracle官方文档中关于该视图的描述

FLASHBACK_TRANSACTION_QUERY

FLASHBACK_TRANSACTION_QUERYdisplays information about all flashback transaction queries in the database.

[td]

Column	Datatype	NULL	Description
XID	RAW(8)		Transaction identifier
START_SCN	NUMBER		Transaction start system change number (SCN)
START_TIMESTAMP	DATE		Transaction start timestamp
COMMIT_SCN	NUMBER		Transaction commit system change number (null for active transactions)
COMMIT_TIMESTAMP	DATE		Transaction commit timestamp (null for active transactions)
LOGON_USER	VARCHAR2(30)		Logon user for the transaction
UNDO_CHANGE#	NUMBER		Undo system change number (1or higher)
OPERATION	VARCHAR2(32)		Forward-going DML operation performed by the transaction: D- Delete I- Insert U- Update B UNKNOWN
TABLE_NAME	VARCHAR2(256)		Name of the table to which the DML applies
TABLE_OWNER	VARCHAR2(32)		Owner of the table to which the DML applies
ROW_ID	VARCHAR2(19)		Rowid of the row that was modified by the DML
UNDO_SQL	VARCHAR2(4000)		SQL to undo the DML indicated byOPERATION

--The End --

xiaobudian

支持

hnjxklj

hnjxklj

皇家禁卫队

看帖子的要发表下看法

皇家禁卫队

看帖子的要发表下看法