-- When objects need to be compiled, it is necessary to see if any dependent object
-- is locked.  When an object such as a package is hung during compilation, the
-- best way to check the reason for such a resource contention situation is to
-- analyze if any dependent object is locked by a database job or
-- materialized view refresh etc.
-- The query below shows the locked object and other details.

SELECT s.osuser, ao.object_name, lo.*
FROM gv$locked_object lo, gv$session s,
     all_objects ao
WHERE  lo.object_id = ao.object_id
AND    lo.session_id = s.sid
;



-- Objects in Cache (owner is sys)

SELECT * FROM v$db_object_cache  WHERE owner = user
;



SELECT   -- s.*,
     s.osuser, TO_CHAR(s.logon_time,'mm/dd/yyyy hh24:mi:ss') logon_time, 
     s.serial#, s.status, s.machine, s.program,
     ao.object_name, lo.*
FROM gv$locked_object lo, gv$session s,
     all_objects ao
WHERE  lo.object_id = ao.object_id
AND    lo.session_id = s.sid
;

SELECT * FROM v$lock WHERE sid = &sid;