I changed Mark's script just a little bit. Point is additional column
with sql statement to kill lock owner proces.
SELECT
decode(LO.locked_mode,1,'NULL',
2,'ROW SHARED',
3,'ROW EXCLUSIVE',
4,'SHARED',
5,'S/ROW EXCLUSIVE',
6,'EXCLUSIVE' ) lock_mode,
decode(L.block,1,'Yes',0,'No') blocking,
L.ctime lock_secs,
S.last_call_et inac_secs,
substr(O.object_name,1,30) object,
S.sid,
S.serial#,
substr(S.username,1,20) usr,
substr(S.machine,1,25) machine,
substr(S.osuser,1,15) os_user,
'ALTER SYSTEM KILL SESSION '''||L.sid||','||S.serial#||'''
IMMEDIATE;' kill_command
FROM
v$locked_object LO,
v$session S,
dba_objects O,
v$lock L
WHERE
S.sid=LO.session_id AND
O.object_id=LO.object_id AND
L.sid=LO.session_id AND
L.id1=LO.object_id;
KAzi
Mark Hooper wrote:
>Here's my lock script...
>
>select a.oracle_username, b.sid, b.serial#, b.osuser, b.machine,
> d.ctime "LOCK TIME (seconds)", c.object_name,
> decode(a.locked_mode, 1, 'NULL', 2, 'ROW SHARED', 3,'ROW EXCLUSIVE',
>4, 'SHARED', 5,'S/ROW EXCLUSIVE', 6,'EXCLUSIVE' ) "LOCK MODE",
> b.last_call_et "INACTIVITY TIME (seconds)", d.block "BLOCKING (1=Yes,
>0=No)"
>from v$locked_object a,
> v$session b,
> dba_objects c,
> v$lock d
>where b.sid = a.session_id
> and c.object_id = a.object_id
> and d.sid = a.session_id
> and d.id1 = a.object_id;
>
>Cheers
>MarkH
>
>-----Original Message-----
>From: Deutsch Gerald
>[mailto:oracledba-ezmlmshield-x67261452.[Email address protected]
>Sent: Wednesday, January 26, 2005 7:59 AM
>To: LazyDBA Discussion
>Subject: AW: DML Locks
>
>
>David,
>
>try joining dba_dml_locks to v$session and to v$sql. This will allow you to
>see which SQL ist executed since when and by whom.
>
>----------------------- snip
>----------------------------------------------------------
>
>select
> a.sid, a.serial#, b.first_load_time, b.last_load_time, b.sql_text,
>a.osuser, l.*
>from
> dba_dml_locks l, v$session a, v$sql b
>where
> l.session_id = a.sid
> a.sql_address = b.address
> and a.sql_hash_value = b.hash_value
>
>----------------------------------------------------------------------------
>-----------
>
>If you've got long running transactions you might want to addionally
>integrate v$session_longops into your join - I'll leave that out in the sake
>of brevity and as an exercise for the reader :-)
>
>As dba_dml_locks is new with 10g and I'm only on 9.2, I'm reasonably
>confident but not 100% sure, that the SESSION_ID from the dba_dml_locks view
>means the SID. If that's not the case, please somebody from this group point
>David to the correct join criteria.
>
>Regards,
>
>g
>
>
>-----Ursprüngliche Nachricht-----
>Von: Poole David
>[mailto:oracledba-ezmlmshield-x52094983.[Email address protected]
>Gesendet: Wednesday, January 26, 2005 11:51 AM
>An: LazyDBA Discussion
>Betreff: DML Locks
>
>
>The following table will show me whether or not a session is blocking
>another, but is it possible to find out how long the session has been
>blocking others?
>
>
>SQL> desc dba_dml_locks;
> Name Null? Type
> ------------------------------- -------- ----
> SESSION_ID NUMBER
> OWNER NOT NULL VARCHAR2(30)
> NAME NOT NULL VARCHAR2(30)
> MODE_HELD VARCHAR2(13)
> MODE_REQUESTED VARCHAR2(13)
> LAST_CONVERT NUMBER
> BLOCKING_OTHERS VARCHAR2(40)
>
>SQL>
>
>
>Systems Operations, Thus Plc.
> .
> . . _
>www.thus.net www.demon.net
>email. david.[Email address protected]
>internal. 740 4156
>external. 0141 566 4156
>mobile. 07880 502093
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these
>terms:http://www.lazydba.com/legal.html
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these
>terms:http://www.lazydba.com/legal.html
>
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
>
>
>
Oracle LazyDBA home page