3.Oracle 閃回特性(Flashback Query、Flashback Table)

標籤：問題   wait   poi   sch   sql   建立表   插入資料   foreign   init   

轉載自：6112607

Oracle 閃回查詢是指標對特定的表來查詢特定的時間段內的資料變化情況來確定是否將表閃回到某一個特定的時刻以保證資料無訛誤存在。
這個特性大大的減少了採用時間點復原所需的工作量以及資料庫離線的時間。閃回查詢通常分為Flashback Query(閃回查詢)，Flashback Table Query(閃回表查詢)，Flashback Version Query(閃回版本查詢)，Flashback Transaction Query(閃回事務查詢)。本文主要講述Flashback Query(閃回查詢)，Flashback Table Query(閃回表查詢)。其餘閃回請參考後續文章。

一、Flashback Query(閃回查詢)

通常用於檢索一條記錄的所有版本，倒退單獨的事務或者倒退從指定時間以來對特定表的所有變化Flashback Query的所有形式取決於UNDO表資料表空間，關於UDNO資料表空間請參考：Oracle 復原(ROLLBACK)和撤銷(UNDO)--1.閃回查詢(Flashback Query)文法        SELECT <column_name_list>FROM <table_name>AS OF <SCN>                            --使用as of scn[WHERE <filter_conditions>][GROUP BY <unaggregated columns>][HAVING <group_filter>][ORDER BY <column_positions_or_name>]SELECT <column_name_list>FROM <table_name>AS OF <TIMESTAMP>                      --使用as of timestamp[WHERE <filter_conditions>][GROUP BY <unaggregated columns>][HAVING <group_filter>][ORDER BY <column_positions_or_name>]--2.示範閃回查詢a.示範使用as of timestamp來進行閃回查詢    [email protected]> create table tb1 as select empno,ename,job,deptno from scott.emp where 1=0;    [email protected]> insert into tb1      2  select empno,ename,job,deptno       3  from scott.emp where empno in(7369,7499,7521,7566);    [email protected]> commit;    [email protected]> select * from tb1;         EMPNO ENAME      JOB           DEPTNO    ---------- ---------- --------- ----------          7369 SMITH      CLERK             20          7499 ALLEN      SALESMAN          30          7521 WARD       SALESMAN          30          7566 JONES      MANAGER           20      [email protected]> select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) from dual;    TO_CHAR(SYSDATE,‘YY‘    -------------------    2010-10-25 17:26:08    [email protected]> delete from tb1 where job=‘SALESMAN‘;    [email protected]> commit;    [email protected]> select * from tb1;         EMPNO ENAME      JOB           DEPTNO    ---------- ---------- --------- ----------          7369 SMITH      CLERK             20          7566 JONES      MANAGER           20          [email protected]> select * from tb1 as of timestamp      2  to_timestamp(‘2010-10-25 17:26:08‘,‘yyyy-mm-dd hh24:mi:ss‘);         EMPNO ENAME      JOB           DEPTNO    ---------- ---------- --------- ----------          7369 SMITH      CLERK             20          7499 ALLEN      SALESMAN          30          7521 WARD       SALESMAN          30          7566 JONES      MANAGER           20        [email protected]> select * from tb1 as of timestamp      2  to_timestamp(‘2010-10-25 17:26:08‘,‘yyyy-mm-dd hh24:mi:ss‘)      3  minus select * from tb1;         EMPNO ENAME      JOB           DEPTNO    ---------- ---------- --------- ----------          7499 ALLEN      SALESMAN          30          7521 WARD       SALESMAN          30b.示範使用as of scn來進行閃回查詢    [email protected]> select current_scn from v$database;    CURRENT_SCN     -----------         2032782     [email protected]> select * from tb1;         EMPNO ENAME      JOB           DEPTNO    ---------- ---------- --------- ----------          7369 Henry      CLERK             20          7566 JONES      MANAGER           20    [email protected]> delete from tb1 where empno=7369;    [email protected]> commit;    [email protected]> select * from tb1 as of scn 2032782;         EMPNO ENAME      JOB           DEPTNO    ---------- ---------- --------- ----------          7369 Henry      CLERK             20          7566 JONES      MANAGER           20          由以上可知，通過閃回查詢獲得所需的記錄資訊，然後來構造新的DML語句並實施其操作來保證資料的完整性。

二、Flashback Table Query(閃回表查詢)

二、Flashback Table Query(閃回表查詢)通過查詢UNDO段來抽取所有已變化的記錄細節，在此基礎之上再構造和執行能夠倒退這些變化的語句表閃回通過執行倒退變化的語句並且該執行是一個事務，所有常用規則在該事務上起作用。表閃回時，表上的觸發器預設被禁用，即該表上的DML觸發器將暫時失效，可以在閃回時指定觸發器是否失效。表閃回需要啟用表上的記錄轉移選項--1.下面給出表閃回的種方式FLASHBACK TABLE <schema_name.table_name>TO SCN <scn_number>            --基於SCN的表閃回[<ENABLE | DISABLE> TRIGGERS]FLASHBACK TABLE <schema_name.table_name>TO TIMESTAMP <timestamp>       --基於TIMESTAMP的表閃回[<ENABLE | DISABLE> TRIGGERS]FLASHBACK TABLE <schema_name.table_name>TO RESTORE POINT <restore_point>   --基於RESTORE POINT的表閃回[<ENABLE | DISABLE> TRIGGERS]--2.示範基於SCN的表閃回下面的示範首先建立表tb_tables，並對錶分幾次插入資料，在完成插入前記錄其SCN號用於後續對其進行閃回create table tb_emp as          --建立示範表tb_empselect empno,ename,job,deptno from scott.emp where 1=0;select table_name,row_movement from user_tables; --查看錶的row movement行為，預設為disableTABLE_NAME                     ROW_MOVE------------------------------ --------TB_EMP                         DISABLEDselect current_scn,systimestamp from v$database;  --擷取系統當前的SCNCURRENT_SCN SYSTIMESTAMP----------- --------------------------------------     661490 01-JAN-11 10.56.28.733000 PM +08:00insert into tb_emp         --插入deptno為10的員工select empno,ename,job,deptno from scott.emp where deptno=10;commit;select current_scn,systimestamp from v$database;  --擷取系統當前的SCNCURRENT_SCN SYSTIMESTAMP----------- --------------------------------------    661510  01-JAN-11 10.56.56.546000 PM +08:00insert into tb_emp        --插入deptno為20的員工select empno,ename,job,deptno from scott.emp where deptno=20;commit;select current_scn,systimestamp from v$database;  --擷取系統當前的SCNCURRENT_SCN SYSTIMESTAMP----------- --------------------------------------    661521  01-JAN-11 10.57.17.358000 PM +08:00 insert into tb_emp       --插入deptno為30的員工select empno,ename,job,deptno from scott.emp where deptno=30;commit;select current_scn,systimestamp from v$database; --擷取系統當前的SCNCURRENT_SCN SYSTIMESTAMP----------- --------------------------------------     661539 01-JAN-11 10.57.37.843000 PM +08:00select deptno,count(*) from tb_emp group by deptno order by 1;    DEPTNO   COUNT(*)---------- ----------        10          3        20          5        30          6flashback table tb_emp to scn  661521;    --將表閃回到scn為，即插入部門號為的記錄之前flashback table tb_emp to scn 661521      --閃回失敗，收到錯誤提示，沒有開啟row movement                *ERROR at line 1:ORA-08189: cannot flashback the table because row movement is not enabled     alter table tb_emp enable row movement;   --開啟表tb_emp表的row movement 功能flashback table tb_emp to scn  661521;    --再次實施閃回，閃回成功select deptno,count(*) from tb_emp group by deptno order by 1;  --記錄中沒有部門為30的記錄    DEPTNO   COUNT(*)---------- ----------        10          3        20          5flashback table tb_emp to scn  661510;    --將表閃回到scn為，即插入部門號為20的記錄之前    select deptno,count(*) from tb_emp group by deptno order by 1;  --記錄中沒有部門為20的記錄    DEPTNO   COUNT(*)---------- ----------        10          3--3.示範基於TIMESTAMP的表閃回使用to timestamp進行表閃回,繼續使用上面建立的表來進行閃回        --使用timestamp將表閃回到插入部門號10為之前    flashback table tb_emp to timestamp  to_timestamp(‘01-JAN-11 10.56.28.733000‘);     flashback table tb_emp to timestamp  to_timestamp(‘01-JAN-11 10.56.28.733000‘) --收到錯誤提示                    *    ERROR at line 1:    ORA-01466: unable to read data - table definition has changed  --表結構發生改變    [email protected]> flashback table tb_emp to scn 661539;  --可以將表閃回到插入部門號為30的記錄之後    Flashback complete.此處示範中收到了錯誤提示，注意對於表閃回，可以多次使用同一類型的閃回方式，可以往前閃回，一旦往前閃回之後，也可以往後進行閃回。但交叉閃回則提示表定義發生了變化。閃回失敗。我們可以再次建立一張類似的新表進行基於timestamp進行閃回，與閃回SCN說不同的是，此處使用了timestamp,此示範在此省略。--4.示範基於RESTORE POINT的表閃回基於RESTORE POINT的表閃回首先要建立適當的閃回點，建立閃回點的方式為: CREATE RESTORE POINT point_name;對於閃回成功之後，無用的閃回點可以及時刪除掉，刪除閃回點的方式為: DROP RESTORE POINT point_name下面對基於RESTORE POINT 閃回進行示範drop table tb_emp purge;  --刪除先前建立的表tb_empcreate table tb_emp    --建立示範表tb_empenable row movementas select empno,ename,job,deptno from scott.emp where 1=0;create restore point zero;   --建立閃回點zeroinsert into tb_emp           --插入deptno為10的員工select empno,ename,job,deptno from scott.emp where deptno=10;commit;create restore point one;    --建立閃回點oneinsert into tb_emp           --插入deptno為20的員工select empno,ename,job,deptno from scott.emp where deptno=20;commit;create restore point two;    --建立閃回點twoinsert into tb_emp           --插入deptno為30的員工select empno,ename,job,deptno from scott.emp where deptno=30;commit;select deptno,count(*) from tb_emp group by deptno order by 1;    DEPTNO   COUNT(*)---------- ----------        10          3        20          5        30          6flashback table tb_emp to restore point two;   --閃回到閃回點two之前select deptno,count(*) from tb_emp group by deptno order by 1;flashback table tb_emp to restore point one;   --閃回到閃回點one之前                    select deptno,count(*) from tb_emp group by deptno order by 1;    DEPTNO   COUNT(*)---------- ----------        10          3        drop restore point two;   --刪除建立的閃回點drop restore point one;drop restore point zero;--5.存在參照關係的表閃回帳戶flasher中表tb1與表tb2存在外鍵關係，表tb1的deptno 參照了表tb2中的deptno列帳戶flasher中表tb1與scott.emp具有相同的表結構，表tb2與表scott.dept具有相同的表結構下面為表tb2新增一個deptno，且為表tb1新增一條記錄create table tb1       --基於表scott.emp來建立表tb1enable row movementas select * from scott.emp ;create table tb2       --基於表scott.dept來建立表tb2enable row movementas select * from scott.dept;alter table tb1        --為表tb1添加主鍵約束add constraint tb1_empno_pk primary key(empno);alter table tb2        --為表tb2添加主鍵約束add constraint tb2_deptno_pk primary key(deptno);alter table tb1        --為表tb1添加外鍵約束add constraint tb1_tb2_deptno_fk foreign key(deptno)references tb2(deptno);insert into tb2        --為表tb2插入一個新部門select 50,‘Customer‘,‘Landon‘ from dual;insert into tb1(empno,ename,job,deptno)   --為表tb1插入一個新的僱員select 8000,‘Robinson‘,‘Clerk‘,50 from dual;commit;select current_scn from v$database;   --獲得當前的scn--- 687444delete from tb1 where empno=8000;     --刪除先前新增的部門delete from tb2 where deptno=50;      --刪除先前新增的僱員commit;flashback table tb1 to scn 687444;    --閃回先前刪除的僱員  /*ERROR at line 1:ORA-02091: transaction rolled back    --提示事務被復原，外鍵沒有找到ORA-02291: integrity constraint (FLASHER.TB1_TB2_DEPTNO_FK) violated - parent key not found */flashback table tb1,tb2 to scn 687444;   --將兩個表同時閃回select empno,ename,deptno,dname          --此時新增的僱員被閃回，部門也被閃回from tb1inner join tb2 using(deptno)where deptno=50;     EMPNO ENAME          DEPTNO DNAME---------- ---------- ---------- --------------      8000 Robinson           50 Customer--6.表閃回的幾個常見問題a.當閃回刪除操作之前，如果某個索引值如主鍵被重用，將導致違反主鍵約束，閃回失敗。b.若閃回所需要的UNDO資訊不存在，將引發ORA-08180：no snapshot found based on specified time(未找到基於指定時間的快照)錯誤c.如果受閃回影響的記錄被其它使用者鎖定，將引發ORA-00054：resource busy and acquire with NOWAIT specified (資源忙碌)錯誤d.表定義在閃回期間不能發生變化，否則導致ORA-01466：unable to read data - table definition has changed(表定義已變化)錯誤e.閃回前未啟用row movement，將收到ORA-08189: cannot flashback the table because row movement is not enabled 錯誤f.對於存在參照關係的情況，建議將主表等一起實施閃回，否則，將收到ORA-02091: transaction rolled back，ORA-02291錯誤g.SYS 模式中的表不能使用表閃回技術

　　

3.Oracle 閃回特性(Flashback Query、Flashback Table)