How to find locks and blockers on table

I want to find oracle locked objects. How can I see lock on table ?

Sometimes, “select * from dba_blockers” does’nt return anything but somebody is blocking one or several row.

This SQL statement returns

select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
gv$locked_object a ,
gv$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

OR

BEGIN
dbms_output.enable(1000000);
for do_loop in (select inst_id, session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
b.object_name object_name, b.object_type object_type
FROM gv$locked_object a, dba_objects b
WHERE xidsqn != 0
and b.object_id = a.object_id)
loop
dbms_output.put_line(‘.’);
dbms_output.put_line(‘Blocking Session : ‘||do_loop.inst_id||’-‘||do_loop.session_id||’-‘||do_loop.oracle_username);
dbms_output.put_line(‘Object (Owner/Name): ‘||do_loop.owner||’.’||do_loop.object_name);
dbms_output.put_line(‘Object Type : ‘||do_loop.object_type);
for next_loop in (select sid from v$lock
where id2 = do_loop.xidsqn
and sid != do_loop.session_id)
LOOP
dbms_output.put_line(‘Sessions being blocked : ‘||next_loop.sid);
end loop;
end loop;
END;

This scripts makes my job as a DBA a bit easier. It uses the gv$view. It’s a good habit to use it even if you’r not in a RAC.
If you use the v$view, it will only give you the information of the instance you are currently logged on.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.