How to identify what locked PL/SQL package (Oracle 10.0.4.2)?

Question

I was trying to recompile PL/SQL package and no avail. because something obtained the lock and that wasn't released for long time. As soon as I kill all sessions I was able to recompile but encounter the same behavior (i.e. locked package) and I wonder what tools are avail to identify what could of obtain it and never release it? This happen on (Oracle 10.2.0.4). Greatly appreciate for your help.

Solution 1

I think you mean 10.2.0.4, as there isn't a 10.0.x.x version series.

select * from v$locked_object lo join dba_objects o on lo.object_id = o.object_id
where o.object_name = 'xxPACKAGE NAMExx' and o.object_type = 'PACKAGE';

Solution 2

select l.session_id, l.owner, l.name, l.type, inst_id, sql_id
     , a.sql_fulltext
     , 'alter system disconnect session '''||s.sid||','||s.serial#||',@'||inst_id||''' immediate' ddl
  from dba_ddl_locks l 
  join gv$session s on s.sid = l.session_id
  join gv$sqlarea a using(inst_id, sql_id)
 where l.name = 'OBJECT_NAME'  
;