DROP TABLE emp;

CREATE TABLE emp (

  empno   NUMBER PRIMARY KEY,

  empname VARCHAR2(16),

  salary  NUMBER

);

INSERT INTO emp (empno, empname, salary) VALUES (111, 'Mike', 555);

COMMIT;

DROP TABLE dept;

CREATE TABLE dept (

  deptno   NUMBER,

  deptname VARCHAR2(32)

);

INSERT INTO dept (deptno, deptname) VALUES (10, 'Accounting');

COMMIT;

--此时,emp和dept表每个有一行数据。每个表具有一行的一个行版本。

--模拟错误地删除

UPDATE emp SET salary = salary + 100 WHERE empno = 111;

INSERT INTO dept (deptno, deptname) VALUES (20, 'Finance');

DELETE FROM emp WHERE empno = 111;

COMMIT;

--另一个事务重新以新的员工名插入员工111

INSERT INTO emp (empno, empname, salary) VALUES (111, 'Tom', 777);

UPDATE emp SET salary = salary + 100 WHERE empno = 111;

UPDATE emp SET salary = salary + 50 WHERE empno = 111;

COMMIT;

--DBA检测到了应用程序错误,必须诊断问题。DBA获取在emp表中对应于111的员工的行版本

SELECT versions_xid XID, versions_startscn START_SCN,

  versions_endscn END_SCN, versions_operation OPERATION,

  empname, salary

FROM emp

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

WHERE empno = 111;

XID               START_SCN    END_SCN O EMPNAME              SALARY

---------------- ---------- ---------- - ---------------- ----------

09001100B2200000   10093466            I Tom                     927

030002002B210000   10093459            D Mike                    555

0800120096200000   10093375   10093459 I Mike                    555

3 rows selected.

--结论：结果是按时间降序排列的。第3行对应于emp表创建之后被插入的行。第2行对应于错误删除. 第1行对应于使用新的姓名的插入。

--DBA确定30002002B210000是错误的事务并使用闪回事务查询来审计这个事务所做的所有改变

SELECT  xid, start_scn, commit_scn, operation, logon_user, undo_sql

FROM flashback_transaction_query

WHERE xid = HEXTORAW('000200030000002D');

Results are similar to:

XID               START_SCN COMMIT_SCN OPERATION LOGON_USER

---------------- ---------- ---------- --------- ------------------------------

UNDO_SQL

--------------------------------------------------------------------------------

030002002B210000   10093452   10093459 DELETE    HR

insert into "HR"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');

030002002B210000   10093452   10093459 INSERT    HR

delete from "HR"."DEPT" where ROWID = 'AAATjuAAEAAAAJrAAB';

030002002B210000   10093452   10093459 UPDATE    HR

update "HR"."EMP" set "SALARY" = '555' where ROWID = 'AAATjsAAEAAAAJ7AAA';

030002002B210000   10093452   10093459 BEGIN     HR

4 rows selected.

--查看错误事务和所有后续事务的详细信息

COLUMN operation FORMAT A9

COLUMN table_name FORMAT A10

COLUMN table_owner FORMAT A11

--注意：这个查询没有指定XID,所以会扫描许多无关的行，降低性能

SELECT xid, start_scn, commit_scn, operation, table_name, table_owner

FROM flashback_transaction_query

WHERE table_owner = 'HR'

AND start_timestamp >=

  TO_TIMESTAMP ('2002-04-16 11:00:00','YYYY-MM-DD HH:MI:SS');

Results are similar to:

XID               START_SCN COMMIT_SCN OPERATION TABLE_NAME TABLE_OWNER

---------------- ---------- ---------- --------- ---------- -----------

02000E0074200000   10093435   10093446 INSERT    DEPT       HR

030002002B210000   10093452   10093459 DELETE    EMP        HR

030002002B210000   10093452   10093459 INSERT    DEPT       HR

030002002B210000   10093452   10093459 UPDATE    EMP        HR

0800120096200000   10093374   10093375 INSERT    EMP        HR

09001100B2200000   10093462   10093466 UPDATE    EMP        HR

09001100B2200000   10093462   10093466 UPDATE    EMP        HR

09001100B2200000   10093462   10093466 INSERT    EMP        HR

8 rows selected.