Re: alter package compile hang

"tkonrath" <tkonrath_at_hotmail.com> wrote in news:1127221674.242168.236890_at_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
>
>

Oracle will not/can not compile a package if another session has it open & is running the package.

The session doing the compilation must wait for all other sessions to stop using/accessing the package.

Stop & think about what is or could be happening. The recompile means that the package could have changed. If Oracle would allow a recompile at any time, how should a session with the package open behave? Should it use the old contents?
Should it use the new contents?
What should be done with DML changes that have not yet been committed?

Simply put, YOUR operating procedures are flawed; not Oracle. You shuold NOT be making code changes while users are in the DB! Received on Tue Sep 20 2005 - 08:36:20 CDT