閃回查詢之閃回表查詢
閃回表查詢:
從概念上說,閃回表很簡單。oracle將會查詢撤銷段以提取已更改的所有行,然後構造並執行將會取消更改的語句。閃回操作是一個單獨的事務,如果可能,它就會抵消以前所有事務的效果。資料庫仍然保持聯機並且正常的執行不受影響,除非行鎖定成為一個問題。
表閃回只是另一個事務,通常的規則仍適用。不同於正常處理的唯一之處是,表上的觸發器對閃回操作預設是禁用的。
表閃回經常涉及一個存在外來鍵關係的表,在這種情況下,幾乎不可避免的是閃回操作會因為違反約束而失敗。為了避免這個問題,語法支援用一條命令閃回多個表。
啟用閃回的第一步是在表上支援行移動。
測試表employees和departments
向表中插入一行資料:
HR@orcl 08-OCT-14>insert into employees values(800,'WATSON','JANE','JWHALE','650.507.9833','13-JAN-08','SH_CLERK',2600,NULL,124,300);
1 row created.
1 row created.
檢視當前的時間,注意要設定好時間格式,如果時間格式不正確,那麼後面進行表閃回會出現找不到snapshot的錯誤:
HR@orcl 08-OCT-14>alter session set nls_date_format='yy-mm-dd hh24:mi:ss';
Session altered.
HR@orcl 14-10-08 07:26:28>select sysdate from dual;
SYSDATE
-----------------
14-10-08 07:26:33
Session altered.
HR@orcl 14-10-08 07:26:28>select sysdate from dual;
SYSDATE
-----------------
14-10-08 07:26:33
接下來刪除該部門和員工,要注意先刪除員工以避免違反約束:
HR@orcl 14-10-08 06:59:05>delete from departments where department_id=300;
delete from departments where department_id=300
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
delete from departments where department_id=300
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
HR@orcl 14-10-08 07:01:15>delete from employees where employee_id=800;
1 row deleted.
HR@orcl 14-10-08 07:01:56>delete from departments where department_id=300;
1 row deleted.
HR@orcl 14-10-08 07:02:11>commit;
Commit complete.
1 row deleted.
HR@orcl 14-10-08 07:01:56>delete from departments where department_id=300;
1 row deleted.
HR@orcl 14-10-08 07:02:11>commit;
Commit complete.
現在嘗試將表閃回到該部門和員工存在的時刻:
HR@orcl 14-10-08 07:02:14>flashback table employees to timestamp to_timestamp('14-10-08 06:59:05','yy-mm-dd hh24:mi:ss');
flashback table employees to timestamp to_timestamp('14-10-08 06:59:05','yy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
flashback table employees to timestamp to_timestamp('14-10-08 06:59:05','yy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
可以看到該操作會失敗,因為預設情況下沒有為任何表啟用行移動(作為表閃回的前提條件):
HR@orcl 14-10-08 07:04:25>select table_name,row_movement from user_tables;
TABLE_NAME ROW_MOVE
------------------------------ --------
LOCATIONS DISABLED
LOC DISABLED
T DISABLED
RETRIED_EMPS DISABLED
REGIONS_BACKUP DISABLED
COUNTRIES DISABLED
SYS_TEMP_FBT DISABLED
EMPLOYEES DISABLED
REGIONS DISABLED
DEPARTMENTS DISABLED
JOB_HISTORY DISABLED
TABLE_NAME ROW_MOVE
------------------------------ --------
JOBS DISABLED
12 rows selected.
TABLE_NAME ROW_MOVE
------------------------------ --------
LOCATIONS DISABLED
LOC DISABLED
T DISABLED
RETRIED_EMPS DISABLED
REGIONS_BACKUP DISABLED
COUNTRIES DISABLED
SYS_TEMP_FBT DISABLED
EMPLOYEES DISABLED
REGIONS DISABLED
DEPARTMENTS DISABLED
JOB_HISTORY DISABLED
TABLE_NAME ROW_MOVE
------------------------------ --------
JOBS DISABLED
12 rows selected.
HR@orcl 14-10-08 07:05:04>alter table employees enable row movement;
Table altered.
HR@orcl 14-10-08 07:06:37>alter table departments enable row movement;
Table altered.
Table altered.
HR@orcl 14-10-08 07:06:37>alter table departments enable row movement;
Table altered.
HR@orcl 14-10-08 07:06:48>select table_name,row_movement from user_tables;
TABLE_NAME ROW_MOVE
------------------------------ --------
LOCATIONS DISABLED
LOC DISABLED
T DISABLED
RETRIED_EMPS DISABLED
REGIONS_BACKUP DISABLED
COUNTRIES DISABLED
SYS_TEMP_FBT DISABLED
EMPLOYEES ENABLED
REGIONS DISABLED
DEPARTMENTS ENABLED
JOB_HISTORY DISABLED
TABLE_NAME ROW_MOVE
------------------------------ --------
JOBS DISABLED
12 rows selected.
TABLE_NAME ROW_MOVE
------------------------------ --------
LOCATIONS DISABLED
LOC DISABLED
T DISABLED
RETRIED_EMPS DISABLED
REGIONS_BACKUP DISABLED
COUNTRIES DISABLED
SYS_TEMP_FBT DISABLED
EMPLOYEES ENABLED
REGIONS DISABLED
DEPARTMENTS ENABLED
JOB_HISTORY DISABLED
TABLE_NAME ROW_MOVE
------------------------------ --------
JOBS DISABLED
12 rows selected.
再次嘗試閃回:
HR@orcl 14-10-08 07:28:46>flashback table employees to timestamp to_timestamp('14-10-08 07:26:33','yy-mm-dd hh24:mi:ss');
flashback table employees to timestamp to_timestamp('14-10-08 07:26:33','yy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found
flashback table employees to timestamp to_timestamp('14-10-08 07:26:33','yy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found
這一次造成失敗的原因更加微妙。閃回試圖透過插入此員工來取消對員工800的刪除---但是員工800屬於部門300,已經刪除了該部門而不存在它。因此,違反了外來鍵約束。要避免此問題可以首先閃回departments表,這樣可以插入部門300.但是,如果閃回涉及多個表和許多DML語句,那麼在邏輯上很難找到一個生效的順序。解決方法就是一起閃回兩個表:
HR@orcl 14-10-08 07:30:39>flashback table employees,departments to timestamp to_timestamp('14-10-08 07:26:33','yy-mm-dd hh24:mi:ss');
Flashback complete.
Flashback complete.
這樣閃回會成功,因為一個事務中同時閃回了兩張表,並且僅在該事務的結尾檢查約束---到那時候資料在邏輯上是一致的。
HR@orcl 14-10-08 07:33:48>select * from employees where employee_id=800;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------------- ---------- ---------- -------------- ---------- -------------
800 WATSON JANE JWHALE 650.507.9833 13-01-08 00:00:00 SH_CLERK 2600 124 300
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------------- ---------- ---------- -------------- ---------- -------------
800 WATSON JANE JWHALE 650.507.9833 13-01-08 00:00:00 SH_CLERK 2600 124 300
HR@orcl 14-10-08 07:36:24>select * from departments where department_id=300;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
300 SUPPORT 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
300 SUPPORT 1700
閃回也可能由於其他原因失敗:
1.如果在刪除和閃回之間重用了鍵值,就會出現主鍵約束。
2.如果沒有足夠的撤銷資訊返回到請求的時間,就會引起一個錯誤ORA-08180,"No snapshot found based on specified time"。
3.如果其他使用者鎖定閃回使用者的任何行,則閃回會失敗並給出一條資訊:ORA-00054:"Resource busy and acquire with NOWAIT specified"。
4.表定義在考察期間內不能改變-------閃回不能跨越DDL,試圖這樣做會產生錯誤:ORA-01446:"Uable to read data---table definition has changed".
5.閃回不適用於處於SYS模式下的表,試著想象一下閃回部分資料字典的結果。
如果閃回由於任何原因失敗了,就會取消閃回操作:將回滾成功執行的任何一部分操作,並且表將處於閃回命令發出之前的狀態。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29800581/viewspace-1314431/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 閃回查詢之閃回版本查詢
- 閃回表、閃回查詢
- 【閃回特性之閃回查詢】使用閃回查詢(select as of)
- 基本閃回查詢和閃回表
- 閃回查詢
- 閃回(關於閃回查詢)
- 閃回刪除、閃回查詢
- 閃回技術一:閃回查詢
- 閃回版本查詢與閃回事務查詢
- oracle閃回查詢Oracle
- 閃回查詢(轉)
- 閃回查詢(1)
- oracle 閃回查詢Oracle
- [閃回特性之閃回版本查詢]Flashback Version Query
- DM7閃回與閃回查詢
- Oracle閃回查詢,閃回版本查詢與閃回事務查詢的使用區別總結Oracle
- Oracle 11G 閃回技術 閃回版本查詢和閃回事務查詢Oracle
- Flashback Query閃回查詢
- oracle的閃回查詢Oracle
- oracle的回閃查詢Oracle
- 閃回查詢(undo sql)SQL
- 閃回版本查詢操作
- 【備份恢復】閃回技術之閃回版本查詢
- DM8 閃回查詢
- 閃回版本查詢技術:
- 【閃回特性之閃回事務查詢】Flashback Transaction Query
- 回閃查詢查詢刪除的資料
- (f)--閃回恢復區---實踐2---閃回表(閃回DML部分資料會用到閃回查詢)
- oracle flashback特性(1.2)--閃回查詢之As of scnOracle
- Oracle 11G 閃回技術 使用Oracle閃回查詢Oracle
- Oracle 11G 閃回技術 使用閃回版本查詢Oracle
- oracle閃回版本查詢學習Oracle
- 閃回技術查詢資料
- Oracle 11g 閃回查詢Oracle
- 閃回版本查詢(Flashback Version Query)
- 閃回查詢恢復過程
- oralce恢復誤刪除的表中的資料(閃回、閃回查詢)
- oracle flashback特性(1.1)--閃回查詢之As of timestampOracle