Thursday, January 28, 2010

who are waiting for same record?

who are waiting for same record?
====================================
Solutions:
=========
1.

SELECT * FROM V$SESSION
WHERE trim(row_wait_obj#||row_wait_file#||row_wait_block#||row_wait_row#) =
(SELECT trim(row_wait_obj#||row_wait_file#||row_wait_block#||row_wait_row#)
FROM (
-------------------------------------------
select sid,d.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) rowidd
from v$session s, dba_objects d
where
s.ROW_WAIT_OBJ# = d.OBJECT_ID
and owner='STLBAS'
and OBJECT_TYPE='TABLE'
and sid=:SID
-------------------------------------------
))


select * from test ---object_name
where rowid='AAARJHAAGAACd13AAA'


2. Any rows returned are direct evidence of the problem
=======================================================

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request


3. can also give interesting information about session waiting for lock.
=======================================================================

$ORACLE_HOME/rdbms/admin/utllockt.sql



4. Script to find locks in the database
=======================================

select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

No comments: