关于事务回滚的相关操作

---实际当前事务正在使用的UNDO空间 UNDO段分配为多个事务使用

select XIDUSN,USED_UBLK*(select value from v$parameter where name='db_block_size')/1024/1024 undo_MB,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as "sysdate",START_TIME,XID,ADDR
from v$transaction where addr='07000109961A9EB0';

select inst_id,sid,sql_id,taddr,event,program,sql_exec_id,status,last_call_et from gv$session where taddr='07000000E3214170';

---dead 事务会滚信息,KTUXESIZ待回滚的大小

select INST_ID,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ,sysdate,KTUXECFL from x$ktuxe where KTUXECFL='DEAD';

 

select inst_id,USN,SLT,SEQ,STATE,UNDOBLOCKSDONE*8/1024 DONE_MB,UNDOBLOCKSTOTAL*8/1024 MB from
gv$fast_start_transactions where STATE = 'RECOVERING' AND inst_id = 1;

select inst_id,USN,SLT,SEQ,STATE,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL from
gv$fast_start_transactions where STATE = 'RECOVERING' AND inst_id = 1;

gv$fast_start_servers

 

---设置完参数需注意是否跑到对面节点继续回滚

--fast_start_parallel_rollback 动态生效

--"_cleanup_rollback_entries"=10000 静态生效

估算死事务恢复时间
declare
l_start number;
l_end number;
begin
select ktuxesiz into l_start from x$ktuxe where ktuxeusn=4907 and ktuxeslt=23;
dbms_lock.sleep(60);
select ktuxesiz into l_end from x$ktuxe where ktuxeusn=4907 and ktuxeslt=23;
dbms_output.put_line('time est hour:'||round(l_end/(l_start-l_end)/60,2));
end;
/

---KILLED状态会话多久能跑完

select inst_id,sid,sql_id,taddr,event,program,sql_exec_id,status,last_call_et from gv$session where sid=1799 and serial#=57969;

select XIDUSN,USED_UBLK,USED_UBLK*(select value from v$parameter where name='db_block_size')/1024/1024 MB,sysdate,START_TIME,XID,ADDR
from v$transaction where addr=(select taddr from v$session where sid=379 and serial#=46737);