閃回（關於閃回查詢）

--整理以前的學習筆記

1、閃回版本查詢：
閃回版本查詢功能依賴於AUM（Automatic Undo Management），AUM指的是採用撤銷表空間記錄來增、刪、改資料的方法。
conn hr/hr
SQL> select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
       *
ERROR at line 1:
ORA-00904: : invalid identifier

SQL> conn /as sysdba
Connected.
SQL> grant execute on dbms_flashback to hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291547

create table emp as select * from employees;

SQL> update emp set salary=salary*1.5 where employee_id=195;

1 row updated.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291590

SQL> delete from emp where employee_id=196;

1 row deleted.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291600

SQL> insert into departments values (660,'Security',100,1700);

1 row created.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291604

SQL> update emp set manager_id=100 where employee_id=195;

1 row updated.

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291631

SQL> update emp set department_id=660 where employee_id=195;

1 row updated.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291663

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291667

兩個事務
在第一個事務中：
將195這條資料的salary由2800改為4200
刪除196這條資料
往departments表中插入660這條資料
將195這條資料的manager_id由123改為100
在第二個事務中：
將195這條資料的department_id由50改為660

SQL> select versions_startscn startscn,versions_endscn endscn,
  2  versions_xid xid,versions_operation oper,
  3  employee_id empid,last_name name,manager_id mgrid,salary sal
  4  from emp
  5  versions between scn 2291547 and 2291667
  6  where employee_id in (195,196);
from emp
     *
ERROR at line 4:
ORA-01466: unable to read data - table definition has changed

為什麼會出現這個錯誤？其實是因為我emp這張表是在我查詢了這個SCN2291547之後建立的，如果versions between的SCN差或者時間差，中間包含了DDL語句，那麼這個閃回查詢會返回錯誤。

即：不能在查詢中使用跨越更改表結構的DDL語句的versions子句。也就是說，只能返回到表上最後一條DDL語句（當然包括建立表本身）後的第一個事務的時間點。

好吧，現在重新來一遍
drop table emp purge;
create table emp as select * from employees;
delete from departments where department_id=660;
commit;
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293439

SQL> update emp set salary=salary*1.5 where employee_id=195;

1 row updated.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293446

SQL> delete from emp where employee_id=196;

1 row deleted.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293460

SQL> insert into departments values (660,'Security',100,1700);

1 row created.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  2293469

SQL> update emp set manager_id=100 where employee_id=195;

1 row updated.

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293482

SQL> update emp set department_id=660 where employee_id=195;

1 row updated.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293496

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293508

兩個事務
在第一個事務中：
將195這條資料的salary由2800改為4200
刪除196這條資料
往departments表中插入660這條資料
將195這條資料的manager_id由123改為100
在第二個事務中：
將195這條資料的department_id由50改為660

select versions_startscn startscn,versions_endscn endscn,
versions_xid xid,versions_operation oper,
employee_id empid,last_name name,manager_id mgrid,salary sal
from emp
versions between scn 2293439 and 2293508
where employee_id in (195,196);
SQL> /

STARTSCN     ENDSCN             XID            O      EMPID        NAME       MGRID           SAL
----------   ----------     -----------------  -    ---------- ----------   ----------  ----------
2293505                     0500040084060000   U        195        Jones        100          4200
2293480       2293505       07002000E0050000   U        195        Jones        100          4200
              2293480                                   195        Jones        123          2800
2293480                     07002000E0050000   D        196        Walsh        124          3100
              2293480                                   196        Walsh        124          3100

從查詢結果中可一看到，首先顯示最新更改，endscn為空是最新的版本，startscn為空是最早的版本

 

select versions_starttime,versions_endtime,versions_operation, id
from sunwg
versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME;
--這個應該是顯示行的所有的DML記錄，不限時間

 

2、閃回事務查詢
conn hr/hr
select * from flashback_transaction_query;--hr需要有execute on dbms_flashback的許可權和select any transaction的許可權

conn /as sysdba
grant execute on dbms_flashback to hr;
grant select any transaction to hr;

SQL> select t.start_scn,t.commit_scn,t.logon_user,
  2  t.operation,t.table_name,t.undo_sql
  3  from flashback_transaction_query t where t.xid='07002000E0050000';
 
 START_SCN COMMIT_SCN LOGON_USER OPERATION  TABLE_NAME      UNDO_SQL
----------- ---------- ---------- ---------- --------------- --------------------
   2293027    2293480 HR         UNKNOWN    EMP            
   2293027    2293480 HR         UNKNOWN    DEPARTMENTS    
   2293027    2293480 HR         UNKNOWN    EMP            
   2293027    2293480 HR         UNKNOWN    EMP            
   2293027    2293480 HR         BEGIN

這裡的operation都已經變成unknown了，說明undo已經過期，如果undo還未過期，那麼operation的值是DML操作的反操作，如insert的反操作是delete，這裡就不重複做實驗了，可以參考http://sunwgneuqsoft.itpub.net/post/34741/456879

要使undo不那麼快過期，可以考慮為undo表空間設定retention guarantee，並延長undo_retention的時間

回頭去查
SQL> select versions_startscn startscn,versions_endscn endscn,
  2  versions_xid xid,versions_operation oper,
  3  employee_id empid,last_name name,manager_id mgrid,salary sal
  4  from emp
  5  versions between scn 2293439 and 2293508
  6  where employee_id in (195,196);
from emp
     *
ERROR at line 4:
ORA-30052: invalid lower limit snapshot expression--下限快照表示式無效，也說明是undo已經過期

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26524307/viewspace-1061377/，如需轉載，請註明出處，否則將追究法律責任。