閃回（關於閃回查詢）
--整理以前的學習筆記
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 閃回表、閃回查詢
- 閃回查詢之閃回版本查詢
- 閃回查詢之閃回表查詢
- 【閃回特性之閃回查詢】使用閃回查詢(select as of)
- 閃回刪除、閃回查詢
- 基本閃回查詢和閃回表
- 閃回技術一：閃回查詢
- 閃回查詢
- DM7閃回與閃回查詢
- 閃回（關於閃回資料庫）資料庫
- oracle閃回查詢Oracle
- 閃回查詢（轉）
- 閃回查詢（1）
- oracle 閃回查詢Oracle
- 閃回版本查詢與閃回事務查詢
- ［閃回特性之閃回版本查詢］Flashback Version Query
- Oracle 11G 閃回技術 閃回版本查詢和閃回事務查詢Oracle
- Oracle閃回查詢，閃回版本查詢與閃回事務查詢的使用區別總結Oracle
- Flashback Query閃回查詢
- oracle的閃回查詢Oracle
- oracle的回閃查詢Oracle
- 閃回查詢（undo sql）SQL
- 閃回版本查詢操作
- (f)--閃回恢復區---實踐2---閃回表（閃回DML部分資料會用到閃回查詢）
- 【備份恢復】閃回技術之閃回版本查詢
- Oracle 11G 閃回技術 使用Oracle閃回查詢Oracle
- Oracle 11G 閃回技術 使用閃回版本查詢Oracle
- DM8 閃回查詢
- 閃回版本查詢技術：
- oracle閃回版本查詢學習Oracle
- 閃回技術查詢資料
- Oracle 11g 閃回查詢Oracle
- 閃回版本查詢（Flashback Version Query）
- 閃回查詢恢復過程
- (f)--閃回恢復區---實踐3---閃回查詢(基於AUM (auto undo managemet))
- 【閃回特性之閃回事務查詢】Flashback Transaction Query
- 回閃查詢查詢刪除的資料
- Oracle 11g 閃回版本查詢Oracle