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.

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.