forked from fatdba/Oracle-Database-Scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
smon_rollback_monitoring.sql
86 lines (70 loc) · 2.88 KB
/
smon_rollback_monitoring.sql
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
PROMPT
PROMPT
PROMPT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT------ /) (\
PROMPT------ .-._((,~~.))_.-,
PROMPT------ `-. @@ ,-'
PROMPT------ / ,o--o. \
PROMPT------ ( ( .__. ) )
PROMPT------ ) `----' (
PROMPT------ / \
PROMPT------ / \
PROMPT------ / \
PROMPT------ "The Silly Cow"
PROMPT----- Script: smon_rollback_monitoring.sql
PROMPT----- Author: Prashant 'The FatDBA'
PROMPT----- Version: V1.5 (Date: 04-02-2010)
PROMPT-----
PROMPT-----
PROMPT-----
spool SMON_RollBack_Progress.lst
Prompt
Prompt Script will run for 10 minutes and checks rollback status from x$ktuxe every 2 mins
Prompt -----------------------------------------------------------------------------------
Prompt
set lines 120
col useg format a30
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;
Prompt
Prompt ------------------------------------------------------------------------------------
Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);
select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;
Prompt
Prompt ------------------------------------------------------------------------------------
Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);
select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;
Prompt
Prompt ------------------------------------------------------------------------------------
Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);
select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;
Prompt
Prompt ------------------------------------------------------------------------------------
Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);
select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;
Prompt ** END OF SCRIPT **
spool off