RE: dml that made locking

The following scripts will identify locking objects and session info.

spool holding_session.lst
column object_name justify c heading "Object|Name"  format a32
column username    justify c heading "User|Name"    format a7
column osuser      justify c heading "OS|User"      format a7
column pid         justify c heading "Ora|Proc|ID"  format 999
column serial#     justify c heading "Ora|Serial|#" format 999999
column sid         justify c heading "Holding|Session"  format 999
column spid        justify c heading "Unix|Proc"    format a5
column object_id   justify c heading "Obj|ID"       format 99999
column lockwait    justify c heading "Lock|Wait"
column type        justify c heading "Lock|Type"    format a4
column lmode       justify c heading "Mode"         format 9999
set pagesize 60 linesize 100
select /*+ RULE */ lck.sid, ses.serial#, pro.pid, pro.spid, obj.object_name,
      obj.object_id, ses.username, ses.osuser,
      lck.type, lck.lmode
from dba_blockers blk, dba_objects obj, v$lock lck,
    v$session ses, v$process pro
where
ses.username is not null and
blk.holding_session = ses.sid
and   lck.id1   = obj.object_id
and   ses.paddr = pro.addr
and   lck.sid   = ses.sid
-- and   ses.paddr = pro.addr
-- and   ses.username is not NULL
order by obj.object_name, ses.lockwait desc, lck.sid;
spool off
clear columns

----

select piece, sql_text
from v$sqltext
where (address,hash_value) in
     (select sql_address,sql_hash_value
      from v$session
      where paddr = (select addr
                     from v$process
                     where spid = '&1'))
order by piece;


From: "Ujang Jaenudin" <ujang.jaenudin@xxxxxxxxx>
Reply-To: ujang.jaenudin@xxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: dml that made locking
Date: Fri, 30 Mar 2007 11:09:36 +0700

dear all,

i have read jk still article about locking,
http://www.jlcomp.demon.co.uk/faq/locked_rows.html

and now i tried to develop a script to auto kill which lock that have
been retained in system for 15 mins, but i need the information about
who is locking (sid,serial) and dml syntax that made locking.

i tried use prev_has_value in v$session and find the sql_text in the
v$sql, but i think the lock holder might query another.....

pls help....

--
regards
ujang
--
http://www.freelists.org/webpage/oracle-l

_________________________________________________________________
Watch free concerts with Pink, Rod Stewart, Oasis and more. Visit MSN Presents today. http://music.msn.com/presents?icid=ncmsnpresentstagline&ocid=T002MSN03A07001 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: