The following query is helpful in figuring out if any objects have been locked during script execution:
select o.object_name, o.subobject_name, o.object_type, l.*
from gv$locked_object l, dba_objects o
where l.object_id = o.object_id;
Another query that can be used to figure out if there is a “deadlock” kind scenario in the database, i.e two or more processes vying for the same resources..
select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
