User Tools

Site Tools


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