Home » RDBMS Server » Server Administration » Want to know the Lock Details
Want to know the Lock Details [message #208702] Mon, 11 December 2006 23:14 Go to next message
rkesavan
Messages: 6
Registered: October 2006
Junior Member
Hai friends

In my EMP table was accessed by 10users. so there is Some Lock Occured.
I want to know the which user is locked with which record.(Both USER and RECORD Details).

Please send me the Query for my problem.


Thanks in advance

with regards
kesavan.r
Re: Want to know the Lock Details [message #208796 is a reply to message #208702] Tue, 12 December 2006 04:49 Go to previous message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member

-- who is blocked
set linesize 120
column lmode format 99 heading LM
column request format 99 heading RQ

select A.sid, serial#, B.type, lmode, request, block, ctime, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session A, v$lock B
where A.sid = B.sid
and lockwait is not null
order by A.sid, B.type;


-- who is blocking
select A.sid, serial#, B.type, lmode, request, block, ctime, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session A, v$lock B
where A.sid = B.sid
and A.sid in
( select sid
from v$lock
where block != 0
)
order by A.sid, B.type;


-- object involved
select object_type, owner, object_name
from dba_objects
where object_id = &1;

-- which sql statement (current sql, not necessary the statement that caused the lock)
select sql_text
from v$sqltext_with_newlines
where (address, hash_value)
= ( select sql_address, sql_hash_value
from v$session
where sid = &sid
)
order by piece;

Previous Topic: Object type, please help....
Next Topic: Creation Date of Table
Goto Forum:
  


Current Time: Sat Dec 03 17:50:47 CST 2016

Total time taken to generate the page: 0.08529 seconds