See Monitoring
See any locking sessionselect * from dba_locks order by blocking_others Use utllockt.sql to see locking sessions Remember that catblock.sql should have ran, you can verify this by using: select * from dba_locks sqlplus (windows)>@?\rdbms\admin\utllockt sqlplus (unix)>@?/rdbms/admin/utllockt WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2 ----------------- ----------------- -------------- -------------- ----------------- ----------------- 20 None 9 DML Exclusive Exclusive 54132 0 In this case the session 20 is locking session 9, so kill session 20 (you may use script above) |
Library cache pin locks http://orafaq.com/faq/how_does_one_diagnose_and_fix_library_cache_pin_waits SELECT sid, event, p1raw, seconds_in_wait, wait_time FROM sys.v_$session_wait WHERE event = 'library cache pin' SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';' , 'kill -9 ' || spid FROM x$kglpn p, v$session s, v$process WHERE p.kglpnuse=s.saddr AND kglpnhdl='DA741F24' and v$process.addr = s.paddr |
| A session is hang, kill anyone interfering
SELECT *
FROM v$access
WHERE OBJECT IN (SELECT object_name
FROM dba_objects
WHERE object_id IN (SELECT object_id
FROM v$locked_object
WHERE session_id = 32))
select 'ALTER SYSTEM DISCONNECT SESSION ''' || sid || ', ' || v$session.serial# ||
''' immediate/*spid=' || spid || '*/'
from v$session, v$process
where sid in (
select sid from v$access
where object = 'VTCONNECTORSTATE'
group by sid
)
and v$process.addr = v$session.paddr
|
Who is locking whatselect * from ( SELECT s.schemaname, p.username, s.sid, p.pid, p.spid, s.username ora, DECODE(l2.type, 'TX','TRANSACTION ROW-LEVEL', 'TS','TEMPORARY SEGMENT ', 'TD','TABLE LOCK', 'TM','ROW LOCK', l2.type ) vlock, DECODE(l2.type, 'TX','DML LOCK', 'TS','TEMPORARY SEGMENT', 'TD',DECODE(l2.lmode+l2.request, 4,'PARSE ' || u.name || '.' || o.name, 6,'DDL', l2.lmode+l2.request), 'TM','DML ' || u.name || '.' || o.name, l2.type ) type, DECODE(l2.lmode+l2.request, 2,'RS', 3 ,'RX', 4 ,'S', 5 ,'SRX', 6 ,'X', l2.lmode+l2.request ) lmode , DECODE(l2.request, 0,NULL, 'WAIT') wait FROM v$process p, v$_lock l1, v$lock l2, v$resource r, sys.obj$ o, sys.user$ u, v$session s WHERE s.paddr = p.addr AND s.saddr = l1.saddr AND l1.raddr = r.addr AND l2.addr = l1.laddr AND l2.type <> 'MR' AND l2.type <> 'RT' AND r.id1 = o.obj# (+) AND o.owner# = u.user# (+) ) |
Lock simulationThe updates performed in this example does not modify the actual values!
select id, de_VC1 from prova where id in (193556, 196756)
ID DE_VC1
---------- --------------------
193556 mon
196756 mon
session 1, sid 145>update prova set de_VC1 = 'sess1' where id = 193556;
1 row updated.
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id = 145
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 DML Row-X (SX) None 53176 0 270 Not Blocking
ALDO 145 Transaction Exclusive None 196615 676 270 Not Blocking
session 2, sid 156>update prova set de_VC1 = 'sess2' where id = 196756;
1 row updated.
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id = 156
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 156 DML Row-X (SX) None 53176 0 270 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 270 Not Blocking
session 1, sid 145>update prova set de_VC1 = 'sess1' where id = 196756;
--session hang waiting
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id IN (145, 156)
ORDER BY session_id
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 Transaction Exclusive None 196615 676 883 Not Blocking
ALDO 145 DML Row-X (SX) None 53176 0 883 Not Blocking
ALDO 145 Transaction None Exclusive 262191 680 81 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 349 Blocking
ALDO 156 DML Row-X (SX) None 53176 0 349 Not Blocking
session 2, sid 156>update prova set de_VC1 = 'sess2' where id = 193556;
--session hang waiting
session 1, sid 145>ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
session 2, sid 156>--continue waiting
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id IN (145, 156)
ORDER BY session_id
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 Transaction Exclusive None 196615 676 1246 Blocking
ALDO 145 DML Row-X (SX) None 53176 0 1246 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 712 Not Blocking
ALDO 156 Transaction None Exclusive 196615 676 9 Not Blocking
ALDO 156 DML Row-X (SX) None 53176 0 712 Not Blocking
|
| Locking lock table vpo.garanzia_s in exclusive mode nowait; |