(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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DM7閃回與閃回查詢
- Oracle 11G 閃回技術 閃回版本查詢和閃回事務查詢Oracle
- DM8 閃回查詢
- Orcale利用閃回功能恢復資料
- 2.6.3 指定閃回區
- Oracle 11G 閃回技術 使用Oracle閃回事務查詢Oracle
- Flashback Drop閃回刪除功能實踐
- Oracle閃回功能恢復偶然丟失的資料(轉)Oracle
- Oracle閃回技術 為Oracle閃回配置資料庫Oracle資料庫
- MySQL閃回技術之binlog2sql恢復binlog中的SQLMySql
- 【DB寶43】MySQL誤操作閃回恢復利器之my2sqlMySql
- Oracle 19C Data Guard基礎運維-07 failover後閃回恢復dg架構Oracle運維AI架構
- Oracle資料庫閃回區空間不足Oracle資料庫
- Oracle資料庫閃回Oracle資料庫
- flashback query閃回資料
- dg_閃回資料庫實驗資料庫
- Oracle drop分割槽表單個分割槽無法透過閃回恢復Oracle
- 【Flashback】Flashback Drop閃回刪除功能實驗
- rac使用預設閃回區歸檔空間滿
- 【Flashback】Flashback Database閃回資料庫功能實驗Database資料庫
- [20180419]關於閃回的一些問題.txt
- my2sql資料閃回SQL
- 詳解oracle資料庫閃回Oracle資料庫
- 【趙強老師】MySQL的閃回MySql
- 【PDB】pdb閃回，Oracle還原點Oracle
- Oracle 閃回資料庫測試Oracle資料庫
- mysql閃回工具binlog2sqlMySql
- Oracle閃回技術--Flashback Version QueryOracle
- 騰訊基於全時態資料庫技術的資料閃回資料庫
- [20180423]關於閃回表與主外來鍵約束.txt
- 工具分享丨資料閃回工具MyFlash
- 【RECO_ORACLE】Oracle閃回PDB的方法Oracle
- [20180423]表空間閃回與snapshot standby
- Oracle回收站表閃回機制研究Oracle
- MySQL工具之binlog2sql閃回操作MySql
- 閃迪隨身碟資料恢復資料恢復
- 【Oracle 12c資料庫支援閃回庫功能】實驗Oracle資料庫
- 一個非常老但是很有用的功能-閃回
- ORACLE 閃回檢視v$flashback_database_log/statOracleDatabase