| Want to know the Lock Details [message #208702] |
Mon, 11 December 2006 23:14  |
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  |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
