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
Oracle LazyDBA home page