Re: Getting database hangs on package rebuilds

Hi!

Here is _our_ policy on what can be done and what can't be done while the database is open for users:

Can be done:
Creation of new objects (tables, packages, indexes, triggers etc). Replacement of package bodys.

Can't be done:
Replacement of package specifications.
Anything else that can cause other objects to be invalid, such as 'Alter table' and replacing views.

We do not use stand-alone stored functions/procedures, they are all kept in packages.

Keeping package specification and package body in different files is crucial to offer patches that can be installed while the database is open. In practice, changes in package specifications don't occur often compared to changes in package bodys.

Trying to replace package specifications while the database is open for users can cause error saying something about timeout occured while waiting to lock the package. I think the chances for getting this error increases if you use global variables inside your package.

Of course many will say that you should not do _any_ installations while the database is open for users. They are, on a conceptual level, right, but we have distributed ca one hundred small patches like this over the years. And especially the DBAs appreciate it, it gives them less work.

There may be differences in do's and dont's between your system and our's, so this was just meant as an input to your work trying to find your own way around...

Regards, Njål

Here is the problem. When we push code in our production environment all of our sessions lock up because they cannot aquire a latch to pin and load a specific package. Our current work around is to crash the instance when this problem comes up. Does anybody know how to best diagnose this? What we are seeing is after the package is rebuilt, v$session_wait shows an increasing number of sessions show the "library cache pin" or "library cache load" event. If I try to execute a function from the package, my session will hang. If anyone can give me some places to look to find out what is going on here, I'd appreciate it. Received on Sun Sep 02 2001 - 10:30:32 CDT