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
   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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: