Re: What object is being locked?

From: simone griffin <simonemg_at_online.no>
Date: 24 Oct 1998 06:50:17 GMT
Message-ID: <01bdff19$3b1466e0$3ec74382@IT-StepUser>

Hi.

Take a look at these script:

table_locks.sql

---

--This script helps to identify which users currently have what objects locked.

SELECT s.osuser, s.username, s.sid, a.object tablename, a.owner,

       decode(a.ob_typ,2,'DDL','DML') lock_mode FROM sys.v_$session S,

       sys.v_$access A

WHERE  a.sid = s.sid  
AND    a.object   like nvl(upper('&TableName'||'%'),'%')
AND    s.osuser   like nvl(lower('&OS_User'||'%'),'%')
AND    s.username like nvl(lower('&UserName'||'%'),'%')
AND    a.ob_typ in (2,4) 

ORDER
   BY 1, 2, 3, 4

waiters.sql

---

This script pinpoint who is blocking whom.

SELECT substr(s1.username,1,12)    "WAITING User",
       substr(s1.osuser,1,8)            "OS User",
       substr(to_char(w.session_id),1,5)    "Sid",
       P1.spid                              "PID",
       substr(s2.username,1,12)    "HOLDING User",
       substr(s2.osuser,1,8)            "OS User",
       substr(to_char(h.session_id),1,5)    "Sid",
       P2.spid                              "PID"
FROM   sys.v_$process P1,   sys.v_$process P2,
       sys.v_$session S1,   sys.v_$session S2,
       sys.dba_locks w,     sys.dba_locks h
WHERE  h.mode_held        = 'None'
AND    h.mode_held        = 'Null'
AND    w.mode_requested  != 'None'
AND    w.lock_type (+)    = h.lock_type
AND    w.lock_id1  (+)    = h.lock_id1
AND    w.lock_id2  (+)    = h.lock_id2
AND    w.session_id       = S1.sid  (+)
AND    h.session_id       = S2.sid  (+)
AND    S1.paddr           = P1.addr (+)
AND    S2.paddr           = P2.addr (+)

what.sql

---

Ones you have determined who is blocking whom via the waiters.sql script, you are now able to
drill down and query what the offending user is aclually doing. You can define the application module
that needs to be reviewed.

SELECT /*+ ORDERED */

       s.sid, s.username, s.osuser, 
       nvl(s.machine, '?') machine, 
       nvl(s.program, '?') program,
       s.process F_Ground, p.spid B_Ground, 
       X.sql_text
FFROM   sys.v_$session S,
       sys.v_$process P, 
       sys.v_$sqlarea X
WHERE  s.osuser      like lower(nvl('&OS_User','%'))
AND    s.username    like upper(nvl('&Oracle_User','%'))
AND    s.sid         like nvl('&SID','%')
AND    s.paddr          = p.addr 
AND    s.type          != 'BACKGROUND' 

AND s.sql_address = x.address
AND s.sql_hash_value = x.hash_value
ORDER
    BY S.sid

from command-line
SQL> @what         

	Enter value for OS_User:
	Enter value for Oracle_User:
	Enter value for SID:  16      ( See waiters,sql)

Hope this is of any help.

Best regards
Jan Fure ( using my wifes computer.......)

atran_at_ers.state.tx.us wrote in article <70ij8n$vvq$1_at_nnrp1.dejanews.com>...
> Does anyone have a script that will tell me what objects are being
locked?
> vaguely remember seeing the script somewhere before that joins the value
of
> ID2 (or ID1?) from the v$lock table to DBA_OBJECTS.OBJECT_ID to
> object name and type. Obviously, it was NOT
because I
> tried.
> seems like

in
> advance,
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

>
Received on Sat Oct 24 1998 - 01:50:17 CDT