alter package compile hang

From: tkonrath <tkonrath_at_hotmail.com>
Date: 20 Sep 2005 06:07:54 -0700
Message-ID: <1127221674.242168.236890@g14g2000cwa.googlegroups.com>

We use a lot of packages in the database. There are a lot of dependency among packages in our database. Sometimes, when developers push a new version of a core package (changed its spec), it will invalid some other packages. We constantly have customers activities that need call packages. And sometimes, oracle was unable to recompile invalid package in time. Then the database seems to be in a deadlock, but in our SQL Client Toad, no deadlook is shown. The session, which recompiles a package just hangs with the folowing information: Object Name: ".", Lock Type: Cursor Binds, Lock Mode: Exclusive Mode, Request Type: None). The current
statement of the session is:

/* Formatted on 2005/09/13 18:14 (Formatter Plus v4.7.0) */ BEGIN
   /* NOP UNLESS A TABLE OBJECT */
   IF dictionary_obj_type = 'TABLE' AND SYS.dbms_cdc_publish.active > 0    THEN

      SYS.dbms_cdc_publish.change_table_trigger (dictionary_obj_owner,
                                                 dictionary_obj_name,
                                                 sysevent
                                                );

   END IF;
END; It just stops working.

After doing research with Google we thing it could be a problem because of libray cache locks but we are not shure.

When the next user wants to access a package and recompiles it, this session also stops working with the same statement, but does not set a look.

It seems that one session has a library cache lock and wait for library cache pin, while the other sessions hold library cache pin waiting for library cache lock, and the session tries to recompile packages will never finish its job.

But why dosn't fisnish the first session his recompile job?

Thanks for any help!

Thomas Received on Tue Sep 20 2005 - 08:07:54 CDT