Oracle Flashback Query

FLASHBACK CONFIGURACION ANTES.

connect hr/hr

SQL> select * from JOB_HISTORY as of timestamp (sysdate - (1/24/60*10));

insert into job_history (select * from JOB_HISTORY as of timestamp (sysdate - (1/24/60*10)));


EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
102 13-JAN-93 24-JUL-98 IT_PROG 60
101 21-SEP-89 27-OCT-93 AC_ACCOUNT 110
101 28-OCT-93 15-MAR-97 AC_MGR 110
201 17-FEB-96 19-DEC-99 MK_REP 20
114 24-MAR-98 31-DEC-99 ST_CLERK 50
122 01-JAN-99 31-DEC-99 ST_CLERK 50
200 17-SEP-87 17-JUN-93 AD_ASST 90
176 24-MAR-98 31-DEC-98 SA_REP 80
176 01-JAN-99 31-DEC-99 SA_MAN 80
200 01-JUL-94 31-DEC-98 AC_ACCOUNT 90

10 rows selected.


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 the emp table that correspond to empno 111. The query uses Flashback Version Query pseudocolumns.connect dba_name/password
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
The results table reads chronologically, from bottom to top. The third row corresponds to the version of the row in emp that was originally inserted in the table when the table was created. The second row corresponds to the row in emp that was deleted by the erroneous transaction. The first row corresponds to the version of the row in emp that was reinserted with a new employee name.
The DBA identifies transaction 000200030000002D as the erroneous transaction and issues the following Flashback Transaction Query to audit all changes made by this transaction: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