-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathblocking_locks.sql
executable file
·134 lines (118 loc) · 4.99 KB
/
blocking_locks.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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : rac_locks_blocking.sql |
-- | CLASS : Real Application Clusters |
-- | PURPOSE : Query all Blocking Locks in the databases. This query will |
-- | display both the user(s) holding the lock and the user(s) |
-- | waiting for the lock. This script is RAC enabled. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN locking_instance FORMAT a17 HEAD 'LOCKING|Instance - SID' JUST LEFT
COLUMN locking_sid FORMAT a7 HEAD 'LOCKING|SID' JUST LEFT
COLUMN waiting_instance FORMAT a17 HEAD 'WAITING|Instance - SID' JUST LEFT
COLUMN waiting_sid FORMAT a7 HEAD 'WAITING|SID' JUST LEFT
COLUMN waiter_lock_type HEAD 'Waiter Lock Type' JUST LEFT
COLUMN waiter_mode_req HEAD 'Waiter Mode Req.' JUST LEFT
COLUMN instance_name FORMAT a8 HEAD 'Instance|Name' JUST LEFT
COLUMN sid FORMAT a7 HEAD 'SID' JUST LEFT
COLUMN serial_number FORMAT a7 HEAD 'Serial|Number' JUST LEFT
COLUMN session_status HEAD 'Status' JUST LEFT
COLUMN oracle_user FORMAT a20 HEAD 'Oracle|Username' JUST LEFT
COLUMN os_username FORMAT a20 HEAD 'O/S|Username' JUST LEFT
COLUMN object_owner FORMAT a15 HEAD 'Object|Owner' JUST LEFT
COLUMN object_name FORMAT a20 HEAD 'Object|Name' JUST LEFT
COLUMN object_type FORMAT a15 HEAD 'Object|Type' JUST LEFT
CLEAR BREAKS
prompt
prompt +----------------------------------------------------------------------------+
prompt | BLOCKING LOCKS |
prompt +----------------------------------------------------------------------------+
prompt
SELECT
ih.instance_name || ' - ' || lh.sid locking_instance
, iw.instance_name || ' - ' || lw.sid waiting_instance
, DECODE ( lh.type
, 'CF', 'Control File'
, 'DX', 'Distrted Transaction'
, 'FS', 'File Set'
, 'IR', 'Instance Recovery'
, 'IS', 'Instance State'
, 'IV', 'Libcache Invalidation'
, 'LS', 'LogStartORswitch'
, 'MR', 'Media Recovery'
, 'RT', 'Redo Thread'
, 'RW', 'Row Wait'
, 'SQ', 'Sequence #'
, 'ST', 'Diskspace Transaction'
, 'TE', 'Extend Table'
, 'TT', 'Temp Table'
, 'TX', 'Transaction'
, 'TM', 'Dml'
, 'UL', 'PLSQL User_lock'
, 'UN', 'User Name'
, 'Nothing-'
) waiter_lock_type
, DECODE ( lw.request
, 0, 'None'
, 1, 'NoLock'
, 2, 'Row-Share'
, 3, 'Row-Exclusive'
, 4, 'Share-Table'
, 5, 'Share-Row-Exclusive'
, 6, 'Exclusive'
, 'Nothing-'
) waiter_mode_req
FROM
gv$lock lw
, gv$lock lh
, gv$instance iw
, gv$instance ih
WHERE
iw.inst_id = lw.inst_id
AND ih.inst_id = lh.inst_id
AND lh.id1 = lw.id1
AND lh.id2 = lw.id2
AND lh.request = 0
AND lw.lmode = 0
AND (lh.id1, lh.id2) IN ( SELECT id1,id2
FROM gv$lock
WHERE request = 0
INTERSECT
SELECT id1,id2
FROM gv$lock
WHERE lmode = 0
)
ORDER BY
lh.sid
/
prompt
prompt +----------------------------------------------------------------------------+
prompt | LOCKED OBJECTS |
prompt +----------------------------------------------------------------------------+
prompt
SELECT
i.instance_name instance_name
, RPAD(l.session_id,7) sid
, RPAD(s.serial#,7) serial_number
, s.status session_status
, l.oracle_username oracle_user
, l.os_user_name os_username
, o.owner object_owner
, o.object_name object_name
, o.object_type object_type
FROM
dba_objects o
, gv$session s
, gv$locked_object l
, gv$instance i
WHERE
i.inst_id = l.inst_id
AND l.inst_id = s.inst_id
AND l.session_id = s.sid
AND o.object_id = l.object_id
ORDER BY
l.session_id
/