this scripts is used to monitor transaction rolling back progress
——————————————————————————-
—
— Script: rolling_back.sql
— Purpose: to predict when transactions will finish rolling back
— For: 9.0+
—
— Copyright: (c) Ixora Pty Ltd
— Author: Steve Adams
—
——————————————————————————-
spool save_sqlplus_settings
set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back …
prompt
declare
cursor tx is
select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
from
sys.x$ktuxe x,
sys.v$transaction t,
sys.v$session s
where
x.inst_id = userenv(‘Instance’) and
x.ktuxesta = ‘ACTIVE’ and
x.ktuxesiz > 1 and
t.xidusn = x.ktuxeusn and
t.xidslot = x.ktuxeslt and
t.xidsqn = x.ktuxesqn and
s.saddr = t.ses_addr;
user_name varchar2(30);
xid_usn number;
xid_slot number;
xid_sqn number;
used_ublk1 number;
used_ublk2 number;
begin
open tx;
loop
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
then
sys.dbms_lock.sleep(10);
end if;
select
sum(ktuxesiz)
into
used_ublk2
from
sys.x$ktuxe
where
inst_id = userenv(‘Instance’) and
ktuxeusn = xid_usn and
ktuxeslt = xid_slot and
ktuxesqn = xid_sqn and
ktuxesta = ‘ACTIVE’;
if used_ublk2 < used_ublk1
then
sys.dbms_output.put_line(
user_name ||
'''s transaction ' ||
xid_usn || '.' ||
xid_slot || '.' ||
xid_sqn ||
' will finish rolling back at approximately ' ||
to_char(
sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
'HH24:MI:SS DD-MON-YYYY'
)
);
end if;
end loop;
if user_name is null
then
sys.dbms_output.put_line('No transactions appear to be rolling back.');
end if;
end;
/
prompt
exit;
eg:
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 7 14:54:02 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @rollback
Looking for transactions that are rolling back …
SYS’s transaction 10.3.25785 will finish rolling back at approximately 14:55:52
07-JUL-2012
SQL> begin
2 for i in 1..1000000 loop
3 insert into t1 values (i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> rollback
2 ;
Rollback complete.
SQL> !date
Sat Jul 7 14:55:59 CST 2012
非常准确的时间估算 cool scripts 🙂
