database:oracle:session-locking-package
Check which session is locking a package
Replace the PACKAGE_NAME with the real name of the package.
SELECT s.sid, l.lock_type, l.mode_held, l.mode_requested, l.lock_id1, 'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate' kill_sid FROM dba_lock_internal l, v$session s WHERE s.sid = l.session_id AND UPPER(l.lock_id1) LIKE '%PACKAGE_NAME' AND l.lock_type = 'Body Definition Lock'
Output:
SID | LOCK_TYPE | MODE_HELD | MODE_REQUESTED | LOCK_ID1 | KILL_SID |
---|---|---|---|---|---|
164 | Body Definition Lock | Null | None | USER1.PACKAGE_NAME | alter system kill session '164,64447' immediate |
Check details about the locking session
Replace 164 with the actual sid:
SELECT s.sid, NVL(s.username, 'ORACLE PROC') username, s.osuser, p.spid os_pid, s.program, t.sql_text FROM v$session s, v$sqltext t, v$process p WHERE s.sql_hash_value = t.hash_value AND s.paddr = p.addr AND s.sid = 164 ORDER BY s.sid, t.hash_value, t.piece
The above code may return no results if the sqltext cannot be matched.
In this case, try with the following query:
SELECT s.sid, NVL(s.username, 'ORACLE PROC') username, s.osuser, p.spid os_pid, s.program FROM v$session s, v$process p WHERE s.sid = 164 AND s.paddr = p.addr
database/oracle/session-locking-package.txt · Last modified: 2023/07/04 19:36 by 127.0.0.1