(f)--閃回恢復區---實踐3---閃回查詢(基於AUM (auto undo managemet))
flashback query
閃查的資料來自於回滾段
查詢結果基於時間和版本
閃查的限制
9I 限制5分鐘內
從oracle 9iR1開始， oracle採用了 AUM (auto undo managemet)
9i 版的flashback query由於機制的限制，也存在一些問題和缺點，
如採用時間點的 flashback query，則不能恢復最近5分鐘之內的資料，
也就是說，如果一個資料寫入不到 5分鐘，就被更新或者是刪除了，
這個資料在 9i用flashback query 是查詢不出來的
原因是由於時間和 Scn的同步每5 分鐘的同步一次 smon_scn_time 只存1440 條記錄
9I&10G 都受限undo表空間的大小 ,
undo 表空間的大小 ,直接影響到flashback query 的查詢能力
如果空間太小 ,閃查的維持時間就短,同時太小會有 ORA-01555
undo_retention=n （秒)引數就是資料維持在 ROLLBACK_segment的不強制保持時間
因為要做 DML 就要先申請rollback 空間 無法申請就無法繼續做下去
為了保持新事務正常執行 oracle預設採用了undo_retention 不強制保持時間
就是即使時間不到 ,因空間不足也可以備覆蓋重用
10G 後可以將這個引數時間變成強制 但要保證足夠大的UNDO
SQL> alter tablepsace undotbs1 retention guarantee;
這樣就是不到時間不覆蓋
只針對DML 不閃查 DDL操作
閃查種類可分為
1. 閃回SCN或時間查詢
2. 閃回事務查詢
3. 閃回版本查詢
-----------------------------閃查和閃回scn/timestamp----在“(f)--閃回恢復區---實踐2---閃回表（閃回DML部分資料會用到閃回查詢）”已經介紹了
-----------------------------閃回事務查詢 ---------------------------
transaction query 閃事務查詢
Flashback的事務查詢是透過查詢flashback_transaction_query檢視來實現的
透過查詢該檢視能夠獲得一些事務執行時的資訊，甚至包括UNDO語句。
每個事務都有事務ID 以及SCN關聯關係
SQL> select ename,sal from scott.emp where deptno=10;
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
MILLER 1300
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
661000
SQL> update scott.emp set sal=sal+1 where deptno=10;
3 rows updated.
SQL> update scott.emp set sal=sal+1 where deptno=10;
3 rows updated.
SQL> commit;
Commit complete.
SQL> update scott.emp set sal=sal+0.1 where deptno=10;
3 rows updated.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
661022
SQL> sal+1 的兩次更新在一個事務中 sal+0.1獨立一個事務
SQL> set pages 9999
SQL> select xid,COMMIT_SCN,COMMIT_TIMESTAMP,OPERATION,UNDO_SQL from flashback_transaction_query where TABLE_NAME='EMP' and COMMIT_SCN >= 661000 and COMMIT_SCN <= 661022;
XID COMMIT_SCN COMMIT_TI OPERATION UNDO_SQL
---------------- ---------- --------- -------------------------------- -------------------------------
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1302' where ROWID = 'AAAMjOAAEAAAAAcAAN';
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5002' where ROWID = 'AAAMjOAAEAAAAAcAAI';
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2452' where ROWID = 'AAAMjOAAEAAAAAcAAG';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2451' where ROWID = 'AAAMjOAAEAAAAAcAAG';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5001' where ROWID = 'AAAMjOAAEAAAAAcAAI';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1301' where ROWID = 'AAAMjOAAEAAAAAcAAN';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1300' where ROWID = 'AAAMjOAAEAAAAAcAAN';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMjOAAEAAAAAcAAI';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2450' where ROWID = 'AAAMjOAAEAAAAAcAAG';
9 rows selected.
SQL> 看事務ID來區分事務
透過查詢該檢視能夠獲得一些事務執行時的資訊，甚至包括UNDO語句。
每個事務都有事務ID 以及SCN關聯關係
SQL> select ename,sal from scott.emp where deptno=10;
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
MILLER 1300
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
661000
SQL> update scott.emp set sal=sal+1 where deptno=10;
3 rows updated.
SQL> update scott.emp set sal=sal+1 where deptno=10;
3 rows updated.
SQL> commit;
Commit complete.
SQL> update scott.emp set sal=sal+0.1 where deptno=10;
3 rows updated.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
661022
SQL> sal+1 的兩次更新在一個事務中 sal+0.1獨立一個事務
SQL> set pages 9999
SQL> select xid,COMMIT_SCN,COMMIT_TIMESTAMP,OPERATION,UNDO_SQL from flashback_transaction_query where TABLE_NAME='EMP' and COMMIT_SCN >= 661000 and COMMIT_SCN <= 661022;
XID COMMIT_SCN COMMIT_TI OPERATION UNDO_SQL
---------------- ---------- --------- -------------------------------- -------------------------------
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1302' where ROWID = 'AAAMjOAAEAAAAAcAAN';
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5002' where ROWID = 'AAAMjOAAEAAAAAcAAI';
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2452' where ROWID = 'AAAMjOAAEAAAAAcAAG';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2451' where ROWID = 'AAAMjOAAEAAAAAcAAG';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5001' where ROWID = 'AAAMjOAAEAAAAAcAAI';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1301' where ROWID = 'AAAMjOAAEAAAAAcAAN';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1300' where ROWID = 'AAAMjOAAEAAAAAcAAN';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMjOAAEAAAAAcAAI';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2450' where ROWID = 'AAAMjOAAEAAAAAcAAG';
9 rows selected.
SQL> 看事務ID來區分事務
-----------------------------閃回版本查詢 ---------------------------
閃版版本查詢使用偽列 獲取一段時間內的版本
偽列:versions_starttime、versions_endtime、versions_xid、versions_operation
versions_startscn version_starttime
操作時的SCN和時間 如果為空 表示該行在查詢範圍之外建立
versions_endscn versions_endtime
失效時的SCN和時間 如果為空 表示該行被刪除或在查詢範圍內無改動
versions_xid
事務ID
versions_operation
該行被執行的操作 I(insert) D(delete) U(update)
minvalue maxvalue
版本的最大時間值和最小時間值
SQL> !date "+%F %T"
2011-02-27 00:15:05
SQL>
col versions_starttime for a20
col versions_endtime for a20
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,ename,sal
from scott.emp versions between timestamp to_timestamp('2011-02-27 00:15:05', 'YYYY-MM-DD HH24:MI:SS')
and maxvalue order by VERSIONS_STARTTIME ;
VERSIONS_S VERSIONS_E VERSIONS_XID V ENAME SAL
---------- ---------- ---------------- - ---------- ----------
SCOTT 3003
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
FORD 3000
BLAKE 2850
TURNER 1500
ADAMS 1100
JAMES 950
MARTIN 1250
11 rows selected.
SQL> update scott.emp set sal=sal+1;
11 rows updated.
SQL> commit;
Commit complete.
SQL> update scott.emp set sal=sal+1;
11 rows updated.
SQL> commit;
Commit complete.
SQL> delete scott.emp where deptno=30;
6 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,ename,sal
from scott.emp versions between timestamp to_timestamp('2011-02-27 00:15:05', 'YYYY-MM-DD HH24:MI:SS')
and maxvalue order by VERSIONS_STARTTIME ;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ENAME SAL
-------------------- -------------------- ---------------- - ---------- ----------
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U SCOTT 3004
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U SMITH 801
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U ALLEN 1601
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U BLAKE 2851
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U MARTIN 1251
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U JONES 2976
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U WARD 1251
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U TURNER 1501
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U ADAMS 1101
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U JAMES 951
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U FORD 3001
2011-02-27 00:15:46. 07002100F5000000 U JONES 2977
2011-02-27 00:15:46. 07002100F5000000 U FORD 3002
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U WARD 1252
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U JAMES 952
2011-02-27 00:15:46. 07002100F5000000 U ADAMS 1102
2011-02-27 00:15:46. 07002100F5000000 U SCOTT 3005
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U TURNER 1502
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U BLAKE 2852
2011-02-27 00:15:46. 07002100F5000000 U SMITH 802
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U ALLEN 1602
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U MARTIN 1252
2011-02-27 00:15:58. 01001600E4000000 D ALLEN 1602
2011-02-27 00:15:58. 01001600E4000000 D WARD 1252
2011-02-27 00:15:58. 01001600E4000000 D MARTIN 1252
2011-02-27 00:15:58. 01001600E4000000 D BLAKE 2852
2011-02-27 00:15:58. 01001600E4000000 D TURNER 1502
2011-02-27 00:15:58. 01001600E4000000 D JAMES 952
2011-02-27 00:15:43. FORD 3000
2011-02-27 00:15:43. SCOTT 3003
2011-02-27 00:15:43. ADAMS 1100
2011-02-27 00:15:43. TURNER 1500
2011-02-27 00:15:43. BLAKE 2850
2011-02-27 00:15:43. JONES 2975
2011-02-27 00:15:43. WARD 1250
2011-02-27 00:15:43. ALLEN 1600
2011-02-27 00:15:43. SMITH 800
2011-02-27 00:15:43. JAMES 950
2011-02-27 00:15:43. MARTIN 1250
39 rows selected.
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-758924/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- (f)--閃回恢復區---實踐2---閃回表（閃回DML部分資料會用到閃回查詢）
- Oracle -- 閃回恢復區---實踐1---閃回庫Oracle
- 閃回查詢（undo sql）SQL
- 閃回（關於閃回查詢）
- 閃回查詢恢復過程
- 【備份恢復】閃回技術之閃回版本查詢
- 閃回表、閃回查詢
- 閃回查詢之閃回版本查詢
- 閃回查詢之閃回表查詢
- Oracle閃回恢復區Oracle
- 【閃回特性之閃回查詢】使用閃回查詢(select as of)
- 閃回刪除、閃回查詢
- 閃回查詢恢復誤刪資料
- 基本閃回查詢和閃回表
- 閃回技術一：閃回查詢
- oralce恢復誤刪除的表中的資料（閃回、閃回查詢）
- Oracle閃回查詢，閃回版本查詢與閃回事務查詢的使用區別總結Oracle
- (f)--閃回恢復區-- 並行載入對閃庫的影響並行
- 閃回查詢
- oracle 閃回基於時間的恢復Oracle
- DM7閃回與閃回查詢
- 【備份恢復】閃回資料庫（二） 基於 SCN 閃回資料庫資料庫
- Oracle閃回查詢恢復delete刪除資料Oracledelete
- oracle閃回查詢Oracle
- 閃回查詢（轉）
- 閃回查詢（1）
- oracle 閃回查詢Oracle
- Oracle10g閃回恢復區詳解--開啟，設定閃回區Oracle
- 閃回版本查詢與閃回事務查詢
- ［閃回特性之閃回版本查詢］Flashback Version Query
- Oracle 11G 閃回技術 閃回版本查詢和閃回事務查詢Oracle
- 【備份恢復】 閃回技術之閃回刪除
- 使用閃回查詢恢復誤刪除的資料
- 【備份恢復】閃回資料庫（三）基於時間戳閃回資料庫資料庫時間戳
- Flashback Query閃回查詢
- oracle的閃回查詢Oracle
- oracle的回閃查詢Oracle
- 閃回版本查詢操作