Oracle Locks 1

[AllieJane]

I was wondering if anyone has a simple script that they could pass on which would help me to identiy any locks within an active database.

I know I could use the SQL Lock Manager but I'd rather not.

Many thanks. [sig][/sig]

 

[THoey]

According to the book I got, there is a V$LOCK table that lists the locks currently taken out against this instance. So, try the following:

SELECT * FROM V$LOCK;

HTH
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[AllieJane]

Thanks for that, the problem is that it dosn't give very much information, I was thinking more of a script which tells me if the lock type is
RS - Row Share
RX - Row Exclusive
S - Share
SRX - Share Row Exclusive
X - Exclusive

I found the following script on the internet but was wondering if anyone had any other suggestions - preferably something simpler.

select o.name &quot;TABLE&quot;, s.username, l.type, l.id1 dummy, l.id2 dummy,
decode(l.lmode,
0, '',
1, '*NULL',
2, '*RS',
3, '*RX',
4, '*S',
5, '*SRX',
6, '*X',
'*' || to_char(l.lmode)) ||
decode(l.request,
0, '',
1, ' NULL',
2, ' RS',
3, ' RX',
4, ' S',
5, ' SRX',
6, ' X',
' ' || to_char(l.request)) &quot;MODE&quot;,
a.sql_text
from v$session s, v$sqlarea a, sys.obj$ o, v$lock l
where l.sid = s.sid
and l.id1 = o.obj# (+)
and s.sql_address = a.address
and s.sql_hash_value = a.hash_value
and s.username is not null

Many thanks [sig][/sig]

 

[carp]

You might try running the utllockt.sql script that should by in your ADMIN subdirectory (with all of the other utl scripts). Not only will it give you output of the locks that are being held, but will also show any sessions that are waiting for the locks to go away. [sig][/sig]