RE: dml that made locking
- From: "M Rafiq" <rafiq9857@xxxxxxxxxxx>
- To: ujang.jaenudin@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Sat, 31 Mar 2007 18:12:40 -0400
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
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
