Differences

This shows you the differences between two versions of the page.

--- database:oracle:session-locking-package [2019/10/26 15:12] – created odefta
+++ database:oracle:session-locking-package [2023/07/04 16:36] (current) – external edit 127.0.0.1
@@ Line 9: / Line 9: @@
        l.mode_requested,
        l.lock_id1,
-       'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid
+       '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    UPPER(l.lock_id1) LIKE '%PACKAGE_NAME'
 AND    l.lock_type = 'Body Definition Lock'
 </code>
@@ Line 20: / Line 20: @@
 ^ 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; |
+| 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:
+<code sql>
+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
+</code>
+The above code may return no results if the sqltext cannot be matched. \\
+In this case, try with the following query:
+<code sql>
+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
+</code>