find enqueued rows detail – lock wait block


set serveroutput on
declare
vobject_id number;
vobject_name varchar2(128);
vowner varchar2(30);
vrowid varchar2(200);
begin
for t in ( select ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
 ROW_WAIT_BLOCK#, ROW_WAIT_ROW#,w.event,s.sid, s.username,s.machine, s.module 
from v$session_wait w , v$session s 
where 
w.sid=s.sid and
s.status='ACTIVE' and
w.event in ( 'enqueue','enq: TX - row lock contention')
order by ROW_WAIT_OBJ#,ROW_WAIT_ROW# )
loop
begin
Select owner, object_name,data_object_id into vowner ,
vobject_name,vobject_id from dba_objects where object_id=t.ROW_WAIT_OBJ#;
SELECT DBMS_ROWID.ROWID_CREATE(1,vobject_id,t.ROW_WAIT_FILE#,
t.ROW_WAIT_BLOCK#,t.ROW_WAIT_ROW# ) into vrowid from dual;
dbms_output.put_line
('Select * from '||vowner||'.'||vobject_name||' where rowid='||''''||vrowid||''''||';' );
exception when no_data_found then
goto skip;
end;
<<skip>> null;
end loop;
end;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: