【闪回特性之闪回事务查询】Flashback Transaction Query

        使用闪回事务查询来获取在给定的时间范围内指定事务或所有事务的元数据和历史数据，具体是通过查询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;
   
9. 
   
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;
5. 
   

接着，我们重新插入一条 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30162081/viewspace-1579545/