Posts tagged ‘compile’

February 23, 2011

Compiling package hangs

One customer reports compiling one package hangs for a long time. He confirms to have closing all sessions running that package.

Find who is the running of the package

COLUMN lock_id2 FORMAT A30
select to_char(SESSION_ID,'999') sid ,
   substr(LOCK_TYPE,1,30) Type,
   substr(lock_id1,1,45) Object_Name,
   substr(mode_held,1,4) HELD,
   substr(mode_requested,1,4) REQ,
   lock_id2 lock_addr
FROM dba_lock_internal
WHERE
   mode_requested <> 'None'
   and mode_requested <> mode_held
;

SID  TYPE                                 OBJECT_NAME                                         HELD     REQ  LOCK_ADDR
---- ---------                           --------------------------------------------- ---- ----    ----------------------------------------
 436 Table/Procedure/Type Definitio          CENPROD.TST_PMA                               None Shar  07000001DB7F5C20
 440 Table/Procedure/Type Definitio          CENPROD.TST_PMA                               None Excl  07000001DB7F5C20

The sid=440 is the customer being blocked by compilation, so we killed the session 436 who was running the package and the compilation succeeds.