使用dbms_flashback工具包實現閃回查詢功能

使用dbms_flashback工具包實現閃回查詢功能

Flashback Query是藉助Oracle Undo到期資料而實現的一種方便的邏輯恢複功能。在Undo Tablespace支援的情況下，我們可以查詢到過去一個特定的時間點（或者SCN點）某個資料表的時間版本。

標準的Flashback Query語句是需要藉助as of timestamp| as of scn語句在資料表後面，用於指定查看的資料表過去時間點是什麼。這種方式從資料庫管理員的角度的確是很方便，特別是那些直接存取後台挽救資料的開發管理員。

但是在兩種情況下，as of指定時間的方式存在一些問題。首先是應用程式中的語句，開發嵌入到應用程式的代碼是不能輕易修改的，也就是說我們在procedure或者package的外面，是不能加入那些as of語句指定時間。另一方面，一個時間點資料可能是涉及多個資料表版本操作，逐個表指定是存在很多的問題。於是，使用dbms_flashback包的過去時間點上下文指定功能，就可以解決上面說的問題。

1、環境說明

筆者使用Oracle 11gR2進行測試實驗，具體版本為11.2.0.4。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

當前沒有配置補充日誌supplemental log data，同時Undo配置關鍵參數如下：

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_MIN

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

NO

SQL> show parameter undo

NAME                                TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                      integer    9000

undo_tablespace                      string      UNDOTBS1

dbms_flashback包的描述資訊如下：

SQL> desc dbms_flashback

Element                        Type     

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

ENABLE_AT_TIME                PROCEDURE 

ENABLE_AT_SYSTEM_CHANGE_NUMBER PROCEDURE 

DISABLE                        PROCEDURE 

GET_SYSTEM_CHANGE_NUMBER      FUNCTION 

NOCASCADE                      CONSTANT 

NOCASCADE_FORCE                CONSTANT 

NONCONFLICT_ONLY              CONSTANT 

CASCADE                        CONSTANT 

TRANSACTION_BACKOUT            PROCEDURE

在筆者之前的文章中，經常使用dbms_flashback.get_system_change_number來擷取系統的SCN編號，並且示範過transaction_backout方法逆轉整體事務的策略。本篇集中在enable_at_time、enable_at_system_change_number和disable方法上。

2、dbms_flashback時間機器

enable_at_time和enable_at_system_change_number的作用相同，都是將當前會話的上下文逆轉到過去的一個時間點，區別僅在於制定的是時間點還是SCN編號。

正確執行兩個方法之後，所有的查詢都是基於指定的時間點進行的，類似於電影中的“時間機器”。背後使用的Flashback Query過程根本不需要我們手工指定時間點在資料表後面。

注意：同flashback query使用相同，dbms_flashback方法不允許在SYS使用者下使用，如果使用就會報錯。

SQL> exec dbms_flashback.enable_at_system_change_number(query_scn => 2107410);

begin dbms_flashback.enable_at_system_change_number(query_scn => 2107410); end;

ORA-08185: ???§ SYS ???§??????

ORA-06512: ?? "SYS.DBMS_FLASHBACK", line 12

ORA-06512: ?? line 1

我們的示範實驗會在scott使用者下進行。首先需要給scott使用者賦予dbms_flashback包的執行許可權。

SQL> grant execute on dbms_flashback to scott;

Grant succeeded

切換到scott使用者，建立實驗資料表。

SQL> create table test as select empno, sal from emp where rownum<4;

Table created

SQL> select * from test;

EMPNO      SAL

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

 7369    800.00

 7499  1600.00

 7521  1250.00

此時系統時間和SCN編號如下：

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

                2107631

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH2

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

2015-06-29 13:49:13

之後進行所謂的“誤操作”。

SQL> update test set sal=1000 where empno=7521;

1 row updated

SQL> commit;

Commit complete

SQL> select * from test where empno=7521;

EMPNO      SAL

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

 7521  1000.00

傳統的Flashback Query策略。

SQL> select * from test as of scn 2107631 where empno=7521;

EMPNO      SAL

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

 7521  1250.00

下面使用dbms_flashback方法，指定出一個SCN編號。

SQL> exec dbms_flashback.enable_at_system_change_number(query_scn => 2107631); --開啟了查詢；

PL/SQL procedure successfully completed

SQL> select * from test where empno=7521;

EMPNO      SAL

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

 7521  1250.00

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed

--disable之後，資料恢複

SQL> select * from test where empno=7521;

EMPNO      SAL

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

 7521  1000.00

注意：使用enable方法之後，我們以直接的方式查詢到過去的時間點方法。如果操作結束，需要使用disable方法關閉設定的上下文時間。

如果指定timestamp方法，效果是相同的。

SQL> exec dbms_flashback.enable_at_time(query_time => to_timestamp('2015-06-29 13:49:13','yyyy-mm-dd hh24:mi:ss'));

PL/SQL procedure successfully completed

SQL> select * from test where empno=7521;

EMPNO      SAL

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

 7521  1250.00

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed

最後，我們考慮一下，如果在過去的時間上下文中進行修改，修改相關資料和無關資料，結果是如何呢？

SQL> exec dbms_flashback.enable_at_system_change_number(query_scn => 2107631);

PL/SQL procedure successfully completed

SQL> select * from test where empno=7521;

EMPNO      SAL

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

 7521  1250.00

SQL> select * from test;

EMPNO      SAL

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

 7369    800.00

 7499  1600.00

 7521  1250.00

SQL> delete test where empno=7499;

delete test where empno=7499

ORA-08182: 在閃回模式下操作不受支援

SQL> update test set sal=1000 where empno=7369;

update test set sal=1000 where empno=7369

ORA-08182: 在閃回模式下操作不受支援

SQL> insert into test values (1000,1000);

insert into test values (1000,1000)

ORA-08182: 在閃回模式下操作不受支援

SQL> create table m as select * from test;

create table m as select * from test

ORA-08182: 在閃回模式下操作不受支援

和時間機器一樣，不能改變曆史。

3、結論

Dbms_flashback工具包提供了關於閃回技術的很多功能和有意義的情境。藉助dbms_flashback的flashback query上下文，我們可以方便的實現上下文曆史資料查詢檢索。

1 2 下一頁