|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
|
-------------------------------------------------------------------------------
--
-- Script: rolling_back.sql
-- Purpose: to predict when transactions will finish rolling back
-- For: 9.0+
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-------------------------------------------------------------------------------
@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
@restore_sqlplus_settings
|
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1276714
