flashback version query和 flashback transaction query簡單應用
使用Flashback Version Query 返回在指定時間間隔或SCN間隔內的所有版本,一次commit命令就會建立一個版本。
SQL> create table t (a number,b number);
Table created.
SQL> insert into t values(1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t values(2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> select versions_starttime,versions_xid, versions_operation, a from t versions between timestamp minvalue and maxvalue ; ---執行t表上執行過的所有版本查詢
VERSIONS_STARTTIME VERSIONS_XID VERSIOS-operation A
19-MAR-14 09.22.33 PM 05000500C2000000 I 2
19-MAR-14 09.22.15 PM 03000800C2000000 I 1
Flashback Transaction Query實際上是查詢的資料字典flashback_transaction_query。可以根據flashback_transaction_query 的undo_sql列值返回資料以前版本。
SQL> SELECT /*+ rule */ UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = '03000800C2000000'; --根據事務ID獲取恢復資料的方法
UNDO_SQL
--------------------------------------------------------------------------------
delete from "SYS"."T" where ROWID = 'AAACmiAABAAAHOSAAA';
SQL> delete from "SYS"."T" where ROWID = 'AAACmiAABAAAHOSAAA';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t;--------資料恢復完成
A B
---------- ----------
2 2
小結:
通過version query和transcation query,可以對資料進行有針對行的事務級別的資料恢復。
jiangkch
20140319
--------------------------------------------
SQL> create table t (a number,b number);
Table created.
SQL> insert into t values(1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t values(2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> select versions_starttime,versions_xid, versions_operation, a from t versions between timestamp minvalue and maxvalue ; ---執行t表上執行過的所有版本查詢
VERSIONS_STARTTIME VERSIONS_XID VERSIOS-operation A
19-MAR-14 09.22.33 PM 05000500C2000000 I 2
19-MAR-14 09.22.15 PM 03000800C2000000 I 1
Flashback Transaction Query實際上是查詢的資料字典flashback_transaction_query。可以根據flashback_transaction_query 的undo_sql列值返回資料以前版本。
SQL> SELECT /*+ rule */ UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = '03000800C2000000'; --根據事務ID獲取恢復資料的方法
UNDO_SQL
--------------------------------------------------------------------------------
delete from "SYS"."T" where ROWID = 'AAACmiAABAAAHOSAAA';
SQL> delete from "SYS"."T" where ROWID = 'AAACmiAABAAAHOSAAA';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t;--------資料恢復完成
A B
---------- ----------
2 2
小結:
通過version query和transcation query,可以對資料進行有針對行的事務級別的資料恢復。
jiangkch
20140319
--------------------------------------------
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/393784/viewspace-1125324/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 10g flashback version query 和 flashback transaction query實驗Oracle 10g
- oracle10g之flashback version query 和flashback transaction query 實驗Oracle
- flashback技術之---flashback Transaction Query
- flashback技術之---flashback version query
- flashback總結六之Flashback_Transaction_Query
- 【實驗】【Flashback】Flashback Transaction Query功能實踐
- Oracle10g的Flashback之Flashback Transaction QueryOracle
- flashback總結五之Flashback_Query_Version(上)
- flashback version query in oracle 10gOracle 10g
- 閃回之 Flashback Query (dml表、過程、函式、包等)、Flashback version Query函式
- Flashback Query的應用(轉帖)
- Oracle閃回技術--Flashback Version QueryOracle
- 閃回版本查詢(Flashback Version Query)
- Oracle10g的Flashback version QueryOracle
- flashback總結五之Flashback_Query_Version(下)_補充
- 聊聊閃回事務查詢Flashback Transaction Query
- flashback_transaction_query的資料來源!
- 【Flashback】Flashback Query功能實踐
- flashback技術之---flashback query
- 聊聊閃回版本查詢Flashback Version Query
- 【實驗】【Flashback】Flash Version Query功能實踐
- flashback_transaction_query資料保留的時間
- Flashback Query Benefits (368)
- flashback總結四之Flashback_Query
- oracle flashback特性(1.4)--閃回查詢之Transaction queryOracle
- ORACLE Flashback Query偽列Oracle
- 【閃回特性之閃回事務查詢】Flashback Transaction Query
- 使用FLASHBACK_TRANSACTION_QUERY查詢回滾事務SQLSQL
- [閃回特性之閃回版本查詢]Flashback Version Query
- flashback query閃回資料
- flashback drop/query/table/database/archiveDatabaseHive
- Flashback Query閃回查詢
- Overview of Oracle Flashback Query I (366)ViewOracle
- Overview of Oracle Flashback Query II (367)ViewOracle
- oracle檢視flashback_transaction_query中列operation為unknownOracle
- Oracle OCP 1Z0-053 Q252(Flashback Version Query)Oracle
- 9i新特性之Flashback Query的應用(1)
- 9i新特性之Flashback Query的應用(2)