flashback技术之---flashback Transaction Query

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

  2       FROM flashback_transaction_query

  3       WHERE xid IN (SELECT versions_xid FROM t VERSIONS   between scn 314690 and 314700);

XID              OPERATION   START_SCN COMMIT_SCN LOGON_USER UNDO_SQL

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

01002600C2000000 INSERT         314698     314699 SCOTT      delete from "SCOTT"."T" where ROWID = 'AAALOBAADAAAAASAAC';

01002600C2000000 BEGIN          314698     314699 SCOTT

08001100D4000000 INSERT         314692     314694 SCOTT      delete from "SCOTT"."T" where ROWID = 'AAALOBAADAAAAASAAB';

08001100D4000000 BEGIN          314692     314694 SCOTT

--联合flashback version query和flashback transaction query，可以将用户某个事务做的误操作进行恢复：

SQL> CREATE TABLE emp 

  2     (empno   NUMBER PRIMARY KEY, 

  3      empname VARCHAR2(16), 

  4      salary  NUMBER);

Table created.

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

1 row created.

SQL> commit;

Commit complete.

SQL> CREATE TABLE dept 

  2     (deptno   NUMBER, 

  3      deptname VARCHAR2(32));

Table created.

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

1 row created.

SQL> commit;

Commit complete.

--此时，emp和dept两个表中各自有一条数据

--接下来另一个事务误删了empno=111的数据：

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

1 row updated.

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

1 row created.

SQL> DELETE FROM emp WHERE empno = 111;

1 row deleted.

SQL> commit;

Commit complete.

--另外一个事务插入了一条empno=111的数据，ename与之前的不同

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

1 row created.

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

1 row updated.

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

1 row updated.

SQL> commit;

Commit complete.

--此时，DBA发现了这个错误，需要将empno=111的数据恢复，执行如下查询

SQL> SELECT versions_xid XID, versions_startscn START_SCN,

  2    versions_endscn END_SCN, versions_operation OPERATION,

  3    empname, salary FROM scott.emp

  4    VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

  5    where empno = 111;

XID               START_SCN    END_SCN OPERATION  EMPNAME              SALARY

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

09000800E3000000     367562            I          Tom                     927

06002900BD000000     367525            D          Mike                    555

09000200E3000000     350554     367525 I          Mike                    555

--在查询结果中，sql的实际执行顺序是从下往上执行的，也就是09000200E3000000==>06002900BD000000==>09000800E3000000  

--可以看到事务06002900BD000000执行了delete操作，从flashback_transaction_query视图中查到该事务：

SQL> SELECT  xid, start_scn, commit_scn,operation OP,logon_user,undo_sql 

  2  FROM flashback_transaction_query

  3  WHERE xid = HEXTORAW('06002900BD000000');

XID               START_SCN COMMIT_SCN OP                               LOGON_USER

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

UNDO_SQL

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

06002900BD000000     367517     367525 DELETE                           SCOTT

insert into "SCOTT"."EMP"("EMPNO","EMPNAME","SALARY") values

 ('111','Mike','655');

06002900BD000000     367517     367525 INSERT                           SCOTT

delete from "SCOTT"."DEPT" where ROWID = 'AAALOEAABAAAMraAAB

';

06002900BD000000     367517     367525 UPDATE                           SCOTT

XID               START_SCN COMMIT_SCN OP                               LOGON_USER

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

UNDO_SQL

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

update "SCOTT"."EMP" set "SALARY" = '555' where ROWID = 'AAA

LOCAABAAAMrKAAA';

06002900BD000000     367517     367525 BEGIN                            SCOTT

根据undo_sql，可以将数据恢复。

   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-738213/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-738213/