Flashback Transaction Query and Flashback Version Query:

You use a Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each of the changes made by the transaction.

Let us exploit it with an example….

connect hr/hr

CREATE TABLE emp

   (empno number primary key, empname varchar2(16), salary number);

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

COMMIT;

CREATE TABLE dept (deptno number, deptname varchar2(32));

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

COMMIT;

At this point, emp and dept have one row each. In terms of row versions, each table has one version of one row. Next, suppose that an erroneous transaction deletes employee id 111 from table emp:

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

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

DELETE FROM emp WHERE empno = 111;

COMMIT;

Subsequently, a new transaction reinserts employee id 111 with a new employee name into the emp table.

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

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

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

COMMIT;

At this point, the DBA detects the application error and needs to diagnose the problem. The DBA issues the following query to retrieve versions of the rows in theemp table that correspond to empno 111.

SELECT versions_xid XID, versions_startscn START_SCN,

  versions_endscn END_SCN, versions_operation OPERATION,

  empname, salary FROM hr.emp

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

  where empno = 111;

XID              START_SCN  END_SCN   OPERATION  EMPNAME    SALARY

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

0004000700000058 113855               I          Tom        927

000200030000002D 113564              D          Mike       555

000200030000002E 112670     113564    I          Mike       555

3 rows selected

SELECT  xid, start_scn START, commit_scn COMMIT, 

        operation OP, logon_user USER, 

        undo_sql FROM flashback_transaction_query

        WHERE xid = HEXTORAW('000200030000002D');

XID               START   COMMIT  OP       USER   UNDO_SQL

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

000200030000002D  195243  195244  DELETE   HR     insert into "HR"."EMP" 

("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');

000200030000002D  195243  195244  INSERT   HR     delete from "HR"."DEPT" 

where ROWID = 'AAAKD4AABAAAJ3BAAB';

000200030000002D  195243  195244  UPDATE   HR     update "HR"."EMP" 

set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';

000200030000002D  195243  113565  BEGIN  HR

4 rows selected