oracle 闪回事务
闪回事务，oracle11g才支持的新特性；能够将某个事务完全回滚；
前提：
1.supplemental log data
2.execute dbms_flashback
3.supplemental log data pk
4.select anytransaction
1.设置附加日志数据
--记录dml操作的具体语句
SYS@orcl11g> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SYS@orcl11g> alter database add supplemental log data;
SYS@orcl11g> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
2.授予普通用户dbms_flashback这个包的执行权限
SYS@orcl11g> grant execute on dbms_flashback to hr;
3.设置主键的附加日志数据
SYS@orcl11g> alter database add supplemental log data (primary key) columns;
SYS@orcl11g> select SUPPLEMENTAL_LOG_DATA_PK from v$database;
SUP
---
YES
4.设置查询事务的权限
SYS@orcl11g> grant select any transaction to hr;
5.设置闪回事务的应用场景
HR@orcl11g> select employee_id,salary from hr.employees where employee_id=201;
EMPLOYEE_ID SALARY
----------- ----------
201 13004
6.第一个错误事务
HR@orcl11g> update hr.employees set salary=salary*5;
HR@orcl11g> commit;
7.第二个关联事务
HR@orcl11g> update hr.employees set salary=salary*1.1 where employee_id=201;
HR@orcl11g> commit;
8.查询数据，发现有误
HR@orcl11g> select salary from hr.employees where employee_id=201;
SALARY
----------
71522
9.查询事务信息
SYS@orcl11g> select distinct xid,commit_scn from flashback_transaction_query
2where table_owner='HR'
3and table_name='EMPLOYEES'
4and commit_timestamp > systimestamp - interval '10' minute
5order by commit_scn;
XID COMMIT_SCN
---------------- ----------
14000A0072010000 2026991
0E00010073010000 2027020
10.分别查询对应的undo sql，判断哪个事务是有问题的事务
SYS@orcl11g> select undo_sql from flashback_transaction_query
2* where xid='14000A0072010000'
--发现错误事务，就是14000A0072010000
11.闪回这个事务
SYS@orcl11g> declare
2xids sys.xid_array;
3begin
4 xids := sys.xid_array('14000A0072010000');
5 dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.nocascade);
6end;
7/
declare
*
ERROR at line 1:
ORA-55504: Transaction conflicts in NOCASCADE mode
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
12.必须使用cascade模式，闪回事务，因为多个事务之间有关联
SYS@orcl11g> l
1declare
2xids sys.xid_array;
3begin
4 xids := sys.xid_array('14000A0072010000');
5 dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
6* end;
SYS@orcl11g> /
PL/SQL procedure successfully completed.
13.查询数据
SYS@orcl11g> select employee_id,salary from hr.employees where employee_id=201;
EMPLOYEE_ID SALARY
----------- ----------
201 13004
页:
[1]