ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验】【Flashback】Flashback Transaction Query功能实践

【实验】【Flashback】Flashback Transaction Query功能实践

原创 Linux操作系统 作者:secooler 时间:2009-04-28 13:57:48 0 删除 编辑
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_QUERY displays information about all flashback transaction queries in the database.

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 (1 or 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 by OPERATION

--The End --

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

下一篇: 倒计时25
请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8108524