locks

[hnetscape]

Hi,

How can I find out which tables or table colums are currently locked for which user. And how can I release the lock for other user if I am not able to do dml statements on that table.

Thanks

[julian]

V$LOCKED_OBJECT

[rohitsn]

Hi,
The following is the script to find out which table is locked and what type of lock the session is holding.



set headsep '|'
ttitle "Locks held by the Users | 0=None,1=Null,2=Row Share,3=Row Exclusive | 4=Share,5=Share Row Exclusive,6=Exclusive"

set pagesize 50
set linesize 120

column object_id heading 'Object ID' format 9999999
column object_name heading 'Object name' format a25
column sid heading 'Sid' format 999
column serial# heading 'Serial#' format 999999
column username heading 'Username' format a10
column os_user_name heading 'Username#2' format a20
column locked_mode heading 'locked Mode' format 9


select a.object_id,a.object_name,b.sid,b.serial#,b.username,c.os_user_name,c.locked_mode
from dba_objects a,v$session b,v$locked_object c
where a.object_id=c.object_id and
b.sid=c.session_id;

ttitle off


In case of any help write to me at rohitsn@altavista.com

Regards,
ROhit Nirkhe,Oracle DBA,OCP 8i
rohitsn@altavista.com

[Raminder]

Another method of determining locking issues is to determine info from V$Session where value in LOCKWAIT column is NOT NULL.

[julian]

Don't worry about locking in a non-OPS enviroment.

Thel ocking hell is when you administer OPS :-)

[pando]

Originally posted by julian
Don't worry about locking in a non-OPS enviroment.

Thel ocking hell is when you administer OPS :-)

I wouldnt say that at all, locking can be a serious problem in any environment, for exmaple in crm applications where you have over 7000 connections

[julian]

I wouldnt say that at all, locking can be a serious problem in any environment, for exmaple in crm applications where you have over 7000 connections

Then at least double that problem for Parallel Server Enviroment and imagine the hell it is :-) Or fun, you may put it either way Pando.