【實驗】【Flashback】Flashback Transaction Query功能實踐

secooler發表於2009-04-28
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章