Flashback Version Query、Flashback Transaction Query快速闪回细粒度数据 - 数据库编程

ARCHAR2(256) --dml更改的表 TABLE_OWNER VARCHAR2(32) --表的所有者 ROW_ID VARCHAR2(19) --修改行的ROWID UNDO_SQL VARCHAR2(4000) - -撤销dml的sql语句 --使用闪回事务查询前，必须启用重做日志流的其他日志记录，重做日志流与Log Miner使用的数据相同，只是接口不同。示例说明： SQL> conn / as sysdba; SQL> alter database add supplemental log data; Database altered. SQL> alter database add supplemental log data (primary key) columns; Database altered. SQL> grant select any transaction to scott; Grant succeeded. SQL> conn scott/xyc SQL> update xyc_t1 set sal=9999 where empno=7902; --更改值sal=9999 1 row updated. SQL> commit; Commit complete. SQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; TO_DATE(SYSDATE,'YY ------------------- 2013-10-06 10:14:04 SQL> update xyc_t1 set sal=99999 where empno=7902; --更改值sal=99999 1 row updated. SQL> commit; Commit complete. SQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; TO_DATE(SYSDATE,'YY ------------------- 2013-10-06 10:14:23 SQL> select versions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_operation,empno 2 from xyc_t1 versions between timestamp to_timestamp('2013-10-06 10:14:04','YYYY-MM-DD HH24:MI:SS') and to_timestamp('2013-10-06 10:14:23','YYYY-MM-DD HH24:MI:SS'); VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION EMPNO ----------------- ------------------------------ --------------- ------------------------------ ---------------- -------------------- ---------- 1035726 06-OCT-13 10.14.17 AM 0400040097020000 U 7902 7698 1035726 06-OCT-13 10.14.17 AM 7902 --查询事务id SQL>

select start_scn,commit_scn,logon_user,operation,table_name,undo_sql 2 from flashback_transaction_query 3 where xid=hextoraw('0400040097020000'); START_SCN COMMIT_SCN LOGON_USER OPERATION TABLE_NAME UNDO_SQL ---------- ---------- -------------------- -------------------- -------------------- -------------------------------------------------- 1035724 1035726 SCOTT UPDATE XYC_T1 update "SCOTT"."XYC_T1" set "SAL" = '9999' where R OWID = 'AAASNYAAEAAAAIVAAD'; --查出undo sql SQL> update "SCOTT"."XYC_T1" set "SAL" = '9999' where ROWID = 'AAASNYAAEAAAAIVAAD'; 1 row updated. SQL> select * from xyc_t1; select * from xyc_t1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ---------- 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 9999 20

Flashback Version Query、Flashback Transaction Query快速闪回细粒度数据(二)