【閃回特性之閃回事務查詢】Flashback Transaction Query

Hoegh發表於2015-04-16

        使用閃回事務查詢來獲取在給定的時間範圍內指定事務或所有事務的後設資料和歷史資料，具體是透過查詢FLASHBACK_TRANSACTION_QUERY檢視，其中的UNDO_SQL列顯示與事務中執行的DML語句在邏輯上相反的SQL語句，可以使用這些SQL程式碼來回退在事務中執行的邏輯步驟。也有一些情況下UNDO_SQL的程式碼不是原始事務的準確的反轉的程式碼，例如UNDO_SQL的INSERT操作不可能使用與被刪除的行相同的ROWID，在表中重新插入被刪除的行。
       下面的語句從FLASHBACK_TRANSACTION_QUERY檢視中查詢事務資訊，包括事務ID，操作，操作起始和結束SCN號，登陸使用者以及和事務邏輯操作相反的SQL語句。

點選(此處)摺疊或開啟

  1. SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
  2. FROM flashback_transaction_query
  3. WHERE xid = HEXTORAW('000200030000002D');
       下面語句結合閃回版本查詢，獲取指定時間段內內所有事務ID和登入使用者。

點選(此處)摺疊或開啟

  1. SELECT xid, logon_user
  2. FROM flashback_transaction_query
  3. WHERE xid IN (
  4.   SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP
  5.   TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
  6.   TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
  7. );
       需要注意的是，如果在查詢FLASHBACK_TRANSACTION_QUERY檢視時不限定XID，可能會搜尋很多不相關的記錄，這樣會使效能下降。因此，我們在使用閃回事務查詢時，通常會結合閃回版本查詢一起使用。下面做個測試，演示二者如何配合使用。
首先，在scott使用者下建立測試表dept和emp。

點選(此處)摺疊或開啟

  1. DROP TABLE emp;
  2. CREATE TABLE emp (
  3.   empno NUMBER PRIMARY KEY,
  4.   empname VARCHAR2(16),
  5.   salary NUMBER
  6. );
  7. INSERT INTO emp (empno, empname, salary) VALUES (111, 'Mike', 555);
  8. COMMIT;

  10. DROP TABLE dept;
  11. CREATE TABLE dept (
  12.   deptno NUMBER,
  13.   deptname VARCHAR2(32)
  14. );
  15. INSERT INTO dept (deptno, deptname) VALUES (10, 'Accounting');
  16. COMMIT;
現在emp和dept各有一條記錄，就行版本而言，每個表各有一個行版本。現在，我們模擬一個誤操作，刪除emp表中emono=111的記錄。

點選(此處)摺疊或開啟

  1. UPDATE emp SET salary = salary + 100 WHERE empno = 111;
  2. INSERT INTO dept (deptno, deptname) VALUES (20, 'Finance');
  3. DELETE FROM emp WHERE empno = 111;
  4. COMMIT;

接著，我們重新插入一條emono=111的記錄，這次換了一個新的員工名稱empname。

點選(此處)摺疊或開啟

  1. INSERT INTO emp (empno, empname, salary) VALUES (111, 'Tom', 777);
  2. UPDATE emp SET salary = salary + 100 WHERE empno = 111;
  3. UPDATE emp SET salary = salary + 50 WHERE empno = 111;
  4. COMMIT;
假設現在我們發現了這個錯誤，然後需要分析問題出在哪兒了，可以執行下面的語句來獲取emp表中empno=111行的所有版本，這裡用到了閃回版本查詢。

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> SELECT versions_xid XID, versions_startscn START_SCN,
  3.   2 versions_endscn END_SCN, versions_operation OPERATION,
  4.   3 empname, salary
  5.   4 FROM emp
  6.   5 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
  7.   6 WHERE empno = 111;
  8.  
  9. XID START_SCN END_SCN OPERATION EMPNAME SALARY
  10. ---------------- ---------- ---------- --------- ---------------- ----------
  11. 0A001900BB180000 3291484 I Tom 927
  12. 0A000000BC180000 3291482 D Mike 555
  13.                                3291482 Mike 555
  14.  
  15. SQL>
查詢結果是按照邏輯的降序排列的，具體的，第三行是最初建表時插入記錄的版本，第二行是誤刪除的行版本，第一行是執行重新插入操作的行版本。由此，我們可以確認0A000000BC180000 是誤操作的事務ID，下面透過閃回事務查尋來查詢該事務所做的所有修改。

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
  3.   2 FROM flashback_transaction_query
  4.   3 WHERE xid = HEXTORAW('0A000000BC180000');
  5.  
  6. XID START_SCN COMMIT_SCN OPERATION LOGON_USER UNDO_SQL
  7. ---------------- ---------- ---------- -------------------------------- ------------------------------ --------------------------------------------------------------------------------
  8. 0A000000BC180000 3291472 3291482 DELETE SCOTT insert into "SCOTT"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655'）；
  9. 0A000000BC180000 3291472 3291482 INSERT SCOTT delete from "SCOTT"."DEPT" where ROWID = 'AAAOhDAAEAAAADQAAB';
  10. 0A000000BC180000 3291472 3291482 UPDATE SCOTT update "SCOTT"."EMP" set "SALARY" = '555' where ROWID = 'AAAOhBAAEAAAADAAAA';
  11. 0A000000BC180000 3291472 3291482 BEGIN SCOTT
  12.  
  13. SQL>
為了檢視誤操作事務和後續事務的所有細節，我們可以執行以下語句。

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> SELECT xid, start_scn, commit_scn, operation, table_name, table_owner
  3.   2 FROM flashback_transaction_query
  4.   3 WHERE table_owner = 'SCOTT'
  5.   4 AND start_timestamp >=
  6.   5 TO_TIMESTAMP ('2015-04-16 11:00:00','YYYY-MM-DD HH:MI:SS');
  7.   
  8.  
  9. XID START_SCN COMMIT_SCN OPERATION TABLE_NAME TABLE_OWNER
  10. ---------------- ---------- ---------- -------------------------------- -------------------------------------------------------------------------------- --------------------------------
  11. 090012009B020000 3291408 3291410 INSERT EMP SCOTT
  12. 0A001900BB180000 3291483 3291484 UPDATE EMP SCOTT
  13. 0A001900BB180000 3291483 3291484 UPDATE EMP SCOTT
  14. 0A001900BB180000 3291483 3291484 INSERT EMP SCOTT
  15. 0A000000BC180000 3291472 3291482 DELETE EMP SCOTT
  16. 0A000000BC180000 3291472 3291482 INSERT DEPT SCOTT
  17. 0A000000BC180000 3291472 3291482 UPDATE EMP SCOTT
  18. 0A001200BC180000 3291470 3291472 INSERT DEPT SCOTT
  19.  
  20. 8 rows selected
  21.  
  22. SQL>




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

相關文章