閃回查詢包括基本的閃回查詢、閃回表、閃回版本查詢、閃回事務查詢。

閃回查詢允許查詢過去某個時間點的資料庫，它可以藉助於一條select語句或透過將會話臨時回退到過去以使其所有查詢都針對先前版本的資料庫來進行。此功能可以用來檢視在提交一組事務之前表的資料狀態。半小時之前的表的狀態是怎樣的呢？透過閃回查詢，可以找出資料對比。利用它還可以選取過去一段時間內一個行的所有版本記錄，從而清楚的瞭解該行上的歷史操作，包括操作時間、操作人和作出每個更改的事務識別符號。

一旦確認了引起問題的事務，Oracle可以自動構造SQL語句來反轉事務的更改，自動執行修復過程。這不是回滾一個已提交的事務，因為關聯式資料庫的規則是不允許這麼做的。但可以構造另一個事務來反轉前一個事務造成的影響。與閃回資料庫不同的是，閃回事務不會丟失資料，所做的其它工作仍然保留，並且資料庫可以保持當前狀態。

閃回表則是如果已經確定針對某個表提交了不正確的操作，可以指示Oracle反轉從特定時間點以來對該表所做的所有更改，而其它表保持當前狀態不變。

在執行任何閃回查詢、閃回事務或閃回表操作期間，資料庫保持開啟狀態，並且所有物件（包括閃回中涉及的物件）都是可以使用的。閃回過程可能需要取消對某些表的完整性和約束的強制檢查，否則閃回操作可能失敗。如一個事務的閃回要求對主鍵列執行插入操作，那麼該鍵值必須不在當前表中。又如一個表有外來鍵約束，那麼不能對該表單獨執行閃回操作，而必須在一個操作中閃回所有相關的表。

相比於閃回資料庫，各種形式的閃回查詢具有更精細的恢復粒度，應該不會對使用者造成任何影響。除了需要授予適當的許可權外，不需要任何配置即可使用。各種形式的閃回查詢只依賴於撤銷表空間的使用，可能要求對撤銷管理做一些調整。所有形式的閃回查詢都是依賴於撤銷表空間資料來重構它在過去某個時間點的資料。閃回查詢可以回退到多久以前取決於撤銷表空間中是否有撤銷資料可用，如果構造過時的結果則查詢將會失敗。

1、基本的閃回查詢

閃回查詢的語法接受時間戳或SCN的形式。可以查詢以前某個時間點的資料庫，但不能對過去版本的資料執行DML操作。以下示例說明了閃回查詢的基本用法，並利用閃回查詢恢復了被刪除的資料。

1）看一下當前時間

select sysdate from dual;

SYSDATE

-------------------

2016-03-14 22:44:57

2）連線到cmes使用者，刪除表c_line_t中的一些資料

conn cmes/cmes

select line_id, line_name from c_line_t;

LINE_ID LINE_NAME

---------- --------------------------------------------------

120 維修

80 MZ前懸左

100 MZ副車架

19540 MZ前懸右

35960 MZ前減

86360 MZ後橋

87360 MZ後減

87740 MZ后角

delete from c_line_t where line_name like 'MZ%';

commit;

select line_id, line_name from c_line_t;

LINE_ID LINE_NAME

---------- --------------------------------------------------

120 維修

3）閃回查詢歷史資料

select line_id, line_name from c_line_t as of timestamp to_timestamp('2016-03-14 22:44:50', 'yyyy-mm-dd hh24:mi:ss');

LINE_ID LINE_NAME

---------- --------------------------------------------------

19540 MZ前懸右

87740 MZ后角

35960 MZ前減

80 MZ前懸左

100 MZ副車架

120 維修

87360 MZ後減

86360 MZ後橋

4）插入被刪記錄，確認資料恢復

insert into c_line_t (select * from c_line_t as of timestamp to_timestamp('2016-03-14 22:44:50', 'yyyy-mm-dd hh24:mi:ss') minus select * from c_line_t);

commit;

select line_id, line_name from c_line_t;

有時我們還希望能將整個會話回退到過去某個時間點來完成一系列的歷史查詢，這可以透過使用dbms_flashback程式包來完成，它可以將整個會話設定為閃回模式

exec dbms_flashback.enable_at_time(to_timestamp('2016-03-05 15:14:15', 'yyyy-mm-dd hh24:mi:ss'));

從此以後，該會話所有的查詢將停留在指定的時間點上，而其它會話不受影響。如果需要取消這一設定，可以執行以下命令

exec dbms_flashback.disable;

閃回查詢的另外一種形式是基於SCN的

select count(*) from c_line_t as of scn <SCN>;

而SCN可以藉助檢視v$archived_log參考歷史時間段確定

col name for a80

select name, first_change#, next_change#, first_time from v$archived_log;

2、閃回表操作

表的閃回操作是一個單獨的事務，它試圖抵消以前事務的效果，這並不是去回滾已經提交的事務，關聯式資料庫的規則不允許這麼做，而只能是用一個新事務來取消已提交工作的影響。

閃回表操作維護所有的索引並強制執行約束，唯一不同之處，表上的觸發器對閃回操作預設是禁用的。表的閃回經常會涉及到存在外來鍵關係的表，在這種情況下，幾乎不可避免的是閃回操作會因為違反約束而失敗。為解決此問題，語法支援用一條閃回命令閃回多個表，這將作為單個事務來執行，並在最後檢查約束。

表閃回的第一步是啟用表的行移動功能，這在資料字典中是透過設定標誌來通知Oracle可能改變了行ID。一條記錄的行ID實際上是永遠不會變的，但閃回操作使它看起來好像變了。

以下舉例說明閃回表的操作：

1）在MES資料庫裡我們先為Oracle預設安裝時自帶的scott模式解鎖，這將可以連線到scott使用者，並看到其下有幾張表

sqlplus / as sysdba

alter user scott identified by tiger account unlock;

conn scott/tiger

select * from cat;

TABLE_NAME TABLE_TYPE

------------------------------ -----------

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

這裡員工表EMP和部門表DEPT之間存在外來鍵關係，就是說EMP表中的每個員工必定是部門表DEPT中某個部門的成員。

col constraint_name for a20

col table_name for a20

col r_constraint_name for a20

select constraint_name, constraint_type, table_name, r_constraint_name, status from user_constraints where table_name = 'EMP';

CONSTRAINT_NAME CON TABLE_NAME R_CONSTRAINT_NAME STATUS

-------------------- --- -------------------- -------------------- ------------------------

PK_EMP P EMP ENABLED

FK_DEPTNO R EMP PK_DEPT ENABLED

2）在部門表DEPT中新增一個部門，並在員工表中新增一名員工屬於該部門，之後看一下時間

insert into dept values(50, 'AUTOMATION', 'NANJING');

commit;

insert into emp values(8001, 'LIULUNING', 'MANAGER', 7698, '2012-05-01', 4000, 0, 50);

commit;

select sysdate from dual;

SYSDATE

-------------------

2016-03-19 15:27:38

3）刪除該部門和員工，要注意應首先刪除員工再刪除部門，以避免違反約束

delete from emp where empno = 8001;

delete from dept where deptno = 50;

commit;

4）嘗試將表閃回到該部門及其員工存在的時刻

flashback table emp to timestamp to_timestamp('2016-03-19 15:27:38', 'yyyy-mm-dd hh24:mi:ss');

...

ORA-08189: 因為未啟用行移動功能, 不能閃回表

操作失敗，因為沒有開啟行移動，這是表閃回的前提條件。於是為兩個表啟用行移動功能

alter table dept enable row movement;

alter table emp enable row movement;

5）重新嘗試閃回

flashback table emp to timestamp to_timestamp('2016-03-19 15:27:38', 'yyyy-mm-dd hh24:mi:ss');

...

ORA-02091: 事務處理已回退

ORA-02291: 違反完整約束條件 (SCOTT.FK_DEPTNO) - 未找到父項關鍵字

由於違反了外來鍵約束，閃回仍然不能成功。要避免該問題，可以先閃回部門表DEPT，這樣可以插入50部門，然後再閃回員工表EMP，就不會違反約束了。但是，如果閃回涉及多個表和許多DML語句，那麼在邏輯上可能很難找到一個有效的順序。為此，Oracle提供的解決辦法就是允許同時閃回多張表

flashback table emp, dept to timestamp to_timestamp('2016-03-19 15:27:38', 'yyyy-mm-dd hh24:mi:ss');

這樣閃回可以成功，因為這是在一個事務中同時閃回了兩張表，Oracle僅在該事務的結尾檢查約束，此時資料在邏輯上已經一致。

實際中，閃回操作也可能由於其它原因而失敗：

如果在刪除和閃回之間重用了鍵值，就會出現主鍵約束；
如果沒有足夠的撤銷資訊返回到請求的時間；
如果其他使用者鎖定閃回作用的任何行；
閃回不能跨越表的一些DDL操作，如刪除了一列、對錶做了截斷，都是無法閃回的；
閃回不適用於SYS模式下的表，試想一下閃回部分資料字典將會怎樣。

如果閃回因為任何原因而失敗，Oracle就會取消閃回操作，回滾已經成功執行的任何一部分操作，將表處於閃回命令發出之前的狀態。

閃回表語法的變型允許閃回到一個系統變更號，並在操作期間啟用DML觸發器，而不是預設的禁用狀態

flashback table emp, dept to scn 6539425 enable triggers;

3、閃回版本查詢

一個行在它的生命週期內可能改變多次。閃回版本查詢允許檢視一個行所有提交過的版本，包括建立和結束每個版本的時間戳。另外還可以檢視建立了任何特定版本行的事務識別符號，它可以用於閃回事務查詢。

這些資訊是透過每個表的許多偽列來提供的。與閃回相關的偽列包括：

versions_startscn：透過insert或update建立此版本行的scn；
versions_starttime：建立此版本行的時間戳；
versions_endscn：由於delete或update使此版本行失效的scn；
versions_endtime：此版本行失效的時間戳；
versions_xid：建立此版本行的事務的唯一識別符號；
versions_operation：建立此版本行的事務執行的操作，可以是insert、update或delete。

為了檢視這些偽列，必須在查詢中包含versions between關鍵字，並使用兩個常量來表示scn，minvalue指示Oracle檢索撤銷表空間中最早的資訊，maxvalue將作為當前的scn：

select empno, ename, sal, versions_xid, versions_starttime, versions_endtime, versions_operation from emp versions between scn minvalue and maxvalue;

EMPNO ENAME SAL VERSIONS_XID VERSIONS_STARTTIME VERSIONS_ENDTIME V

---------- ---------- ---------- ---------------- ------------------------------ ----------------------------------- -

8020 YAOWEI 3500 05000F00FC050000 19-3月 -16 05.41.54 下午 D

8020 YAOWEI 3500 080011006D060000 19-3月 -16 05.41.33 下午 19-3月 -16 05.41.54 下午 I

8000 LIULUNING 5000 05000400FB050000 19-3月 -16 05.40.09 下午 U

8000 LIULUNING 4000 19-3月 -16 05.40.09 下午

7369 SMITH 800

7499 ALLEN 1600

7521 WARD 1250

7566 JONES 2975

7654 MARTIN 1250

7698 BLAKE 2850

7782 CLARK 2450

7788 SCOTT 3000

7839 KING 5000

7844 TURNER 1500

7876 ADAMS 1100

7900 JAMES 950

7902 FORD 3000

7934 MILLER 1300

版本查詢結果是按照時間降序排列的，應當自下而上理解：

閃回到撤銷表空間保留的最早記錄，編號為8000的員工，在16年3月19日17:40:09之前SAL值為4000，之後對錶做了update操作（versions_operation偽列表示為“U”），SAL值更新為5000。在17:41:33時刻對錶做了一次插入操作（versions_operation偽列表示為“I”），新增了編號為8020的員工，該版本狀態延續到17:41:54時刻，該條新增的記錄又被刪除（versions_operation偽列表示為“D”）。versions_xid偽列則記錄了每個事務的事務標號。

閃回版本查詢的語法也接受使用兩個時間戳來指定時間範圍，如檢視最近一小時內員工編號為8000的所有版本：

select empno, ename, sal, versions_xid, versions_starttime, versions_endtime, versions_operation from emp versions between timestamp(systimestamp - 1/24) and systimestamp where empno = 8000;

閃回版本查詢不能用於外部表、臨時表或v$檢視，原因是這些物件都不生成撤銷資料。當對錶做了截斷操作，或者對錶欄做了刪除列的操作後，閃回版本查詢資訊將丟失，但增加列的DDL操作則不會丟失版本查詢資訊。

4、閃回事務查詢

閃回事務查詢從事務的另外一個角度去考慮撤銷資料：從閃回版本查詢中我們可以看到一個表中某行資料的變化版本及其對應的事務標識。但是，如果一個事務還影響了其它表中的其它行，又如何去跟蹤呢？閃回事務查詢就是要考慮一個事務可能對所有表資料產生的影響，為此，Oracle提供了檢視flashback_transaction_query，利用它可以看到受該事務影響的每個行都對應有一行記錄。

desc flashback_transaction_query

Name Type Nullable Default Comments

---------------- -------------- -------- ------- -----------------------------------------

XID RAW(8) Y 事務識別符號，是閃回版本查詢中顯示的versions_xid偽列的連線列，

如 where xid = hextoraw('…')

START_SCN NUMBER Y 事務開始時的系統變更號

START_TIMESTAMP DATE Y 事務開始時的時間戳

COMMIT_SCN NUMBER Y 事務提交時的系統變更號

COMMIT_TIMESTAMP DATE Y 事務提交時的時間戳

LOGON_USER VARCHAR2(30) Y 執行事務會話的Oracle使用者名稱

UNDO_CHANGE# NUMBER Y 撤銷系統變更號

OPERATION VARCHAR2(32) Y 應用到行的DML操作：insert update delete

TABLE_NAME VARCHAR2(256) Y 行所屬的表

TABLE_OWNER VARCHAR2(32) Y 表所屬模式

ROW_ID VARCHAR2(19) Y 受影響行的rowid

UNDO_SQL VARCHAR2(4000) Y 反轉操作的SQL語句

該檢視會顯示撤銷資料仍保留在撤銷段內的已提交的事務以及進行中的事務。查詢該檢視需要授予select any transaction的許可權。對於活動的事務來說，COMMIT_SCN和COMMIT_TIMESTAMP列都是NULL，因為還沒有提交。對於已回滾的事務則不會顯示。由於這裡XID列是RAW型別的，而版本查詢中versions_xid偽列是十六進位制的，因此查詢時必須使用型別轉換。另外，要想在查詢中顯示出UNDO_SQL反轉SQL語句是有條件的，資料庫必須開啟補充日誌（Supplemental Log）。下面的例子將會說明閃回事務查詢的使用。

1）我們利用MES資料庫裡scott使用者的表emp，並再建立一張表emp1和一個觸發器tri_emp，該觸發器負責對emp表的每行update操作在emp1中做同步更新

sqlplus scott/tiger

create table emp1 as select * from emp;

觸發器程式碼如下：

create or replace trigger tri_emp

after update on emp

for each row

begin

update emp1

set ename = :new.ename,

job = :new.job,

mgr = :new.mgr,

hiredate = :new.hiredate,

sal = :new.sal,

comm = :new.comm,

deptno = :new.deptno

where empno = :new.empno;

end;

2）檢視資料庫補充日誌是否開啟，查詢反轉SQL需要開啟補充日誌，這個需要登入到DBA下操作

sqlplus / as sysdba

select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

顯示NO表示沒有開啟，現在開啟補充日誌

alter database add supplemental log data;

若要關閉補充日誌，則使用以下語句

alter database drop supplemental log data;

補充日誌也是記錄在聯機redo日誌中的，所以啟用補充日誌會加快聯機redo日誌的填充。

3）在scott模式下對emp表插入一名編號為8020的新員工資訊，並試圖更新將編號為8000員工的工資增加10%，但誤寫為乘上10，並做了提交

insert into emp values(8020, 'YAOWEI', 'MANAGER', 7698, '2016-03-20', 4500, NULL, 50);

commit;

update emp set sal = sal * 10 where empno = 8000;

commit;

4）之後發現了錯誤，利用閃回版本查詢檢視錶的歷史變更：

select empno, ename, sal, versions_operation, versions_xid from emp versions between scn minvalue and maxvalue;

EMPNO ENAME SAL V VERSIONS_XID

---------- ---------- ---------- - ----------------

8000 LIULUNING 50000 U 06001400DC050000

8020 YAOWEI 4500 I 0800210083060000

8000 LIULUNING 5000

7369 SMITH 800

7499 ALLEN 1600

7521 WARD 1250

7566 JONES 2975

7654 MARTIN 1250

7698 BLAKE 2850

7782 CLARK 2450

7788 SCOTT 3000

7839 KING 5000

7844 TURNER 1500

7876 ADAMS 1100

7900 JAMES 950

7902 FORD 3000

7934 MILLER 1300

從下往上看，首先是做了一個8020員工的插入操作，之後對8000員工做了資料更新，SAL值由原來的5000更新為50000，並顯示出了DML操作的事務號。

5）授予scott使用者閃回事務查詢的許可權

grant select any transaction to scott;

6）根據上面版本查詢中找到的事務號，執行閃回事務查詢，看看這個錯誤操作可能影響到的所有表資料，並找到反轉對沖語句

col operation for a10

col table_name for a10

col table_owner for a10

col undo_sql for a100

select operation, table_name, table_owner, undo_sql from flashback_transaction_query where xid = hextoraw('06001400DC050000');

OPERATION TABLE_NAME TABLE_OWNER UNDO_SQL

--------------- --------------- --------------- --------------------------------------------------------------------------------

UPDATE EMP1 SCOTT update "SCOTT"."EMP1" set "ENAME" = 'LIULUNING', "JOB" = 'MANAGER', "MGR" = '769

8', "HIREDATE" = TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), "SAL"

= '5000', "COMM" = NULL, "DEPTNO" = '50' where ROWID = 'AAASaOAAEAAAAC7AAA';

UPDATE EMP SCOTT update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAR3sAAEAAAACWAAA';

結果發現，對emp表的這個更新事務除了改變了自身資料，還影響到emp1表的資料變更，原因是觸發器的作用。

7）利用上述查詢中提供的反轉對沖語句，我們可以糾正之前的操作。可以全部執行這些反轉，或者因為我們明白觸發器的作用，只需執行對emp表的更新，將所有資料修復回來

update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAR3sAAEAAAACWAAA';

commit;

檢視flashback_transaction_query透過構造撤銷語句來取消事務，但是對於大型事務而言，單個的執行它們是一項繁重的任務。這種情況下可以使用dbms_flashback程式包，它含有取消事務的過程，操作起來更加方便。它需要授予flashback any table的許可權。我們可以在sysdba下執行操作

exec dbms_flashback.transaction_backout(numtxns=>2, xids=>sys.xid_array('0500130019060000', '02000E00CB050000'), options=>dbms_flashback.cascade);

該過程接受的引數說明：

numtxns：需要取消的事務的數量
xids：由閃回版本查詢可以獲得的事務識別符號的一個列表，形如sys.xid_array('…', '…')
options：有四種常量選擇

cascade，試圖在邏輯上取消事務，因此不會出現約束違規
nocascade（預設值），應用撤銷更改並且不嘗試識別依賴關係，如果存在外來鍵約束關係的表，操作很可能失敗
nonconflict_only，只取消沒有引起問題的行的更改，資料庫仍保持一致，但是一些事務可能不完整
nocascade_force，將按照與提交時相反的次序撤銷SQL語句

注意dbms_flashback.transaction_backout會將完成的各種撤銷更改都保留為未提交狀態，這樣可以提供使用者一個機會來確認完成的操作，確認無誤後使用者再提交更改。

oracle閃回查詢

相關文章