博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
Oracle Flashback Transaction Query with Oracle Flashback Version Query
阅读量:6962 次
发布时间:2019-06-27

本文共 4557 字,大约阅读时间需要 15 分钟。

Oracle Flashback Transaction Query with Oracle Flashback Version Query

In this example, a database administrator does this:

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;

Now emp and dept have one row each. In terms of row versions, each table has one version of one row. Suppose that an erroneous transaction deletes empno 111 from table emp:

UPDATE emp SET salary = salary + 100 WHERE empno = 111;INSERT INTO dept (deptno, deptname) VALUES (20, 'Finance');DELETE FROM emp WHERE empno = 111;COMMIT;

Next, a transaction reinserts empno 111 into the emp table with a new employee name:

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;

The database administrator detects the application error and must diagnose the problem. The database administrator issues this query to retrieve versions of the rows in the emp table that correspond to empno 111. The query uses Oracle Flashback Version Query pseudocolumns:

SELECT versions_xid XID, versions_startscn START_SCN,  versions_endscn END_SCN, versions_operation OPERATION,  empname, salaryFROM empVERSIONS BETWEEN SCN MINVALUE AND MAXVALUEWHERE empno = 111;

Results are similar to:

XID               START_SCN    END_SCN O EMPNAME              SALARY---------------- ---------- ---------- - ---------------- ----------09001100B2200000   10093466            I Tom                     927030002002B210000   10093459            D Mike                    5550800120096200000   10093375   10093459 I Mike                    555 3 rows selected.

The results table rows are in descending chronological order. The third row corresponds to the version of the row in the table emp that was inserted in the table when the table was created. The second row corresponds to the row in emp that the erroneous transaction deleted. The first row corresponds to the version of the row in emp that was reinserted with a new employee name.

The database administrator identifies transaction 030002002B210000 as the erroneous transaction and uses Oracle Flashback Transaction Query to audit all changes made by this transaction:

SELECT  xid, start_scn, commit_scn, operation, logon_user, undo_sqlFROM flashback_transaction_queryWHERE xid = HEXTORAW('000200030000002D');

Results are similar to:

XID               START_SCN COMMIT_SCN OPERATION LOGON_USER---------------- ---------- ---------- --------- ------------------------------UNDO_SQL-------------------------------------------------------------------------------- 030002002B210000   10093452   10093459 DELETE    HRinsert into "HR"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655'); 030002002B210000   10093452   10093459 INSERT    HRdelete from "HR"."DEPT" where ROWID = 'AAATjuAAEAAAAJrAAB'; 030002002B210000   10093452   10093459 UPDATE    HRupdate "HR"."EMP" set "SALARY" = '555' where ROWID = 'AAATjsAAEAAAAJ7AAA'; 030002002B210000   10093452   10093459 BEGIN     HR  4 rows selected.

To make the result of the next query easier to read, the database administrator uses these SQL*Plus commands:

COLUMN operation FORMAT A9COLUMN table_name FORMAT A10COLUMN table_owner FORMAT A11

To see the details of the erroneous transaction and all subsequent transactions, the database administrator performs this query:

SELECT xid, start_scn, commit_scn, operation, table_name, table_ownerFROM flashback_transaction_queryWHERE 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       HR030002002B210000   10093452   10093459 DELETE    EMP        HR030002002B210000   10093452   10093459 INSERT    DEPT       HR030002002B210000   10093452   10093459 UPDATE    EMP        HR0800120096200000   10093374   10093375 INSERT    EMP        HR09001100B2200000   10093462   10093466 UPDATE    EMP        HR09001100B2200000   10093462   10093466 UPDATE    EMP        HR09001100B2200000   10093462   10093466 INSERT    EMP        HR 8 rows selected.

转载于:https://www.cnblogs.com/elontian/p/9156041.html

你可能感兴趣的文章
Git命令
查看>>
aws S3 util demo
查看>>
react native ios 网络请求问题
查看>>
python文件处理
查看>>
Oracle高级应用之物化视图(materialized view)
查看>>
自定义文件上传样式,该方法几乎可以覆盖其他一切浏览原生样式
查看>>
图片上传后即时预览
查看>>
Django滚动logger
查看>>
负载均衡笔记
查看>>
Maven的使用,Nexus建立本地仓库以及Eclipse导入Maven项目(三): 配置篇
查看>>
装系统
查看>>
如何准备BAT技术面试答案(上)——Java研发方向
查看>>
查找算法(1)--二分查找
查看>>
apache tomcat负载均衡总结
查看>>
深入理解Tomcat系列之二:源码调试环境搭建
查看>>
java简易聊天程序
查看>>
Redis-Cluster实战-
查看>>
Maven提高篇系列之四——使用Profile
查看>>
配置sonar、jenkins进行持续审查
查看>>
如何进行桌面的重定向?
查看>>