oracle 闪回事物查询,Oracle 11G 闪回技术 闪回版本查询和闪回事务查询

DROPTABLEemp;

CREATETABLEemp(

empnoNUMBERPRIMARYKEY,

empnameVARCHAR2(16),

salaryNUMBER

);

INSERTINTOemp(empno,empname,salary)VALUES(111,'Mike',555);

COMMIT;

DROPTABLEdept;

CREATETABLEdept(

deptnoNUMBER,

deptnameVARCHAR2(32)

);

INSERTINTOdept(deptno,deptname)VALUES(10,'Accounting');

COMMIT;

--此时,emp和dept表每个有一行数据。每个表具有一行的一个行版本。

--模拟错误地删除

UPDATEempSETsalary=salary+100WHEREempno=111;

INSERTINTOdept(deptno,deptname)VALUES(20,'Finance');

DELETEFROMempWHEREempno=111;

COMMIT;

--另一个事务重新以新的员工名插入员工111

INSERTINTOemp(empno,empname,salary)VALUES(111,'Tom',777);

UPDATEempSETsalary=salary+100WHEREempno=111;

UPDATEempSETsalary=salary+50WHEREempno=111;

COMMIT;

--DBA检测到了应用程序错误,必须诊断问题。DBA获取在emp表中对应于111的员工的行版本

SELECTversions_xid XID,versions_startscn START_SCN,

versions_endscn END_SCN,versions_operationOPERATION,

empname,salary

FROMemp

VERSIONSBETWEENSCN MINVALUEANDMAXVALUE

WHEREempno=111;

XIDSTART_SCNEND_SCN O EMPNAMESALARY

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

09001100B220000010093466I Tom927

030002002B21000010093459D

Mike555

08001200962000001009337510093459I Mike555

3rowsselected.

--结论：结果是按时间降序排列的。第3行对应于emp表创建之后被插入的行。第2行对应于错误删除.第1行对应于使用新的姓名的插入。

--DBA确定30002002B210000是错误的事务并使用闪回事务查询来审计这个事务所做的所有改变

SELECTxid,start_scn,commit_scn,operation,logon_user,undo_sql

FROMflashback_transaction_query

WHERExid=HEXTORAW('000200030000002D');

Resultsaresimilarto:

XIDSTART_SCN

COMMIT_SCNOPERATIONLOGON_USER

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

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

UNDO_SQL

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

030002002B2100001009345210093459DELETEHR

insertinto"HR"."EMP"("EMPNO","EMPNAME","SALARY")values('111','Mike','655');

030002002B2100001009345210093459INSERTHR

deletefrom"HR"."DEPT"whereROWID='AAATjuAAEAAAAJrAAB';

030002002B2100001009345210093459UPDATEHR

update"HR"."EMP"set"SALARY"='555'whereROWID='AAATjsAAEAAAAJ7AAA';

030002002B2100001009345210093459BEGINHR

4rowsselected.

--查看错误事务和所有后续事务的详细信息

COLUMNoperationFORMATA9

COLUMNtable_nameFORMATA10

COLUMNtable_ownerFORMATA11

--注意：这个查询没有指定XID,所以会扫描许多无关的行，降低性能

SELECTxid,start_scn,commit_scn,operation,table_name,table_owner

FROMflashback_transaction_query

WHEREtable_owner='HR'

ANDstart_timestamp>=

TO_TIMESTAMP('2002-04-16

11:00:00','YYYY-MM-DD HH:MI:SS');

Resultsaresimilarto:

XIDSTART_SCN

COMMIT_SCNOPERATIONTABLE_NAME TABLE_OWNER

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

02000E00742000001009343510093446INSERTDEPTHR

030002002B2100001009345210093459DELETEEMPHR

030002002B2100001009345210093459INSERTDEPTHR

030002002B2100001009345210093459UPDATEEMPHR

08001200962000001009337410093375INSERTEMPHR

09001100B22000001009346210093466UPDATEEMPHR

09001100B22000001009346210093466UPDATEEMPHR

09001100B22000001009346210093466INSERTEMPHR

8rowsselected.