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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
Table tvd_lock_info_1
CREATE TABLE tvd_lock_info_1 (
lock_date DATE
, os_locker VARCHAR2(30)
, locker_schema VARCHAR2(30)
, locker_pid VARCHAR2(9)
, os_waiter VARCHAR2(30)
, waiter_schema VARCHAR2(30)
, waiter_pid VARCHAR2(9)
, sql_text_waiter VARCHAR2(200)
);
Table tvd_lock_info_2
CREATE TABLE tvd_lock_info_2 (
lock_date DATE
, wait VARCHAR2(3)
, os_user VARCHAR2(30)
, process VARCHAR2(9)
, locker VARCHAR2(30)
, object_owner VARCHAR2(30)
, object_name VARCHAR2(30)
, program varchar2(50)
)
TABLESPACE users
/
Procédure tvd_prc_lock_info
CREATE OR REPLACE PROCEDURE tvd_prc_lock_info
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO tvd_lock_info_1
( lock_date, os_locker, locker_schema, locker_pid,
os_waiter, waiter_schema, waiter_pid, sql_text_waiter )
SELECT /* first the table-level locks (TM) and mixed TM/TX TX/TM */
SYSDATE
, S_LOCKER.OSUSER OS_LOCKER
, S_LOCKER.USERNAME LOCKER_SCHEMA
, S_LOCKER.PROCESS LOCKER_PID
, S_WAITER.OSUSER OS_WAITER
, S_WAITER.USERNAME WAITER_SCHEMA
, S_WAITER.PROCESS WAITER_PID
, 'Table lock (TM): ' || U.NAME || '.' || O.NAME || ' - Mode held: '||
DECODE(L_LOCKER.LMODE,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: ' || to_char(L_LOCKER.LMODE)) || ' / Mode requested: '||
DECODE(L_WAITER.REQUEST,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: '||to_char(L_WAITER.REQUEST))
SQL_TEXT_WAITER
FROM
OBJ$ O
, USER$ U
, V$LOCK L_WAITER
, V$LOCK L_LOCKER
, V$SESSION S_WAITER
, V$SESSION S_LOCKER
WHERE S_WAITER.SID = L_WAITER.SID
AND L_WAITER.TYPE IN ('TM')
AND S_LOCKER.sid = L_LOCKER.sid
AND L_LOCKER.ID1 = L_WAITER.ID1
AND L_WAITER.REQUEST > 0
AND L_LOCKER.LMODE >0
AND L_WAITER.ADDR != L_LOCKER.ADDR
AND L_WAITER.ID1 = O.OBJ#
AND U.USER# = O.OWNER#
UNION
SELECT /* now the (usual) row-locks TX */
SYSDATE
, S_LOCKER.OSUSER OS_LOCKER
, S_LOCKER.USERNAME LOCKER_SCHEMA
, S_LOCKER.PROCESS LOCK_PID
, S_WAITER.OSUSER OS_WAITER
, S_WAITER.USERNAME WAITER_SCHEMA
, S_WAITER.PROCESS WAITER_PID
, 'TX: ' || O.SQL_TEXT SQL_TEXT_WAITER
FROM
V$LOCK L_WAITER
, V$LOCK L_LOCKER
, V$SESSION S_WAITER
, V$SESSION S_LOCKER
, V$_LOCK1 L1_WAITER
, V$OPEN_CURSOR O
WHERE S_WAITER.SID = L_WAITER.SID
AND L_WAITER.TYPE IN ('TX')
AND S_LOCKER.sid = L_LOCKER.sid
AND L_LOCKER.ID1 = L_WAITER.ID1
AND L_WAITER.REQUEST >0
AND L_LOCKER.LMODE >0
AND L_WAITER.ADDR != L_LOCKER.ADDR
AND L1_WAITER.LADDR = L_WAITER.ADDR
AND L1_WAITER.KADDR = L_WAITER.KADDR
AND L1_WAITER.SADDR = O.SADDR;
INSERT INTO tvd_lock_info_2
( lock_date, wait, os_user, process, locker,
object_owner, object_name, program )
SELECT SYSDATE
, DECODE(L.REQUEST,0,'NO','YES') WAIT
, S.OSUSER
, S.PROCESS
, S.USERNAME LOCKER
, U.NAME T_OWNER
, O.NAME OBJECT_NAME
, ' '||S.PROGRAM PROGRAM
FROM
V$LOCK L
, USER$ U
, OBJ$ O
, V$SESSION S
WHERE U.USER# = O.OWNER#
AND S.SID = L.SID
AND L.ID1 = O.OBJ#
AND L.TYPE = 'TM'
AND U.NAME != 'SYS'
UNION
SELECT SYSDATE
, DECODE(L.REQUEST,0,'NO','YES') WAIT
, S.OSUSER
, S.PROCESS
, S.USERNAME LOCKER
, '-'
, 'Record(s)'
, ' '||S.PROGRAM PROGRAM
FROM V$LOCK L
, V$SESSION S
WHERE S.SID = L.SID
AND L.TYPE = 'TX';
COMMIT;
END;
/
show errors
Trigger log_deadlock_errors
CREATE OR REPLACE TRIGGER log_deadlock_errors
AFTER SERVERERROR
ON DATABASE
BEGIN
IF (IS_SERVERERROR (60)) THEN
sys.tvd_prc_lock_info;
END IF;
END log_deadlock_errors;
/
show errors |
