RE: dml that made locking

From: M Rafiq <rafiq9857_at_hotmail.com>
Date: Sat, 31 Mar 2007 18:12:40 -0400
Message-ID: <BAY134-F20B6BA0EEE986C689BC4ACA1620@phx.gbl>

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_at_gmail.com> Reply-To: ujang.jaenudin_at_gmail.com
To: oracle-l_at_freelists.org
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 Received on Sat Mar 31 2007 - 17:12:40 CDT