Re: PL/SQL: could not find program unit...

Yes, I recognize the error message 'program unit not found'. The shared pool is fragmented and the rdbms can not find enough contiguous space to hold the complete package specifications and body in the shared pool.

The quick fix is to try to clear some pool space using the command 'alter system flush shared pool;' If the package is not too large this will work.

1. Brute force - Make the shared pool bigger (much bigger)
2. Use the dbms_shared_pool.keep('owner.package_name,'P') command to pin the package into the shared pool at startup. Note the package only loads the specification into the pool under version 7.1.3 and lower so you can still get the 'module not found' or a storage error (?4341). To load the body into memory create a dummy procedure in all your packages that you can execute upon database startup to load the complete package into memory.

In the specification: procedure loadme; -- DBA standard procedure In the body: procedure loadme is

                   begin
                      null;
                    end;
                    end loadme;

Under paraller server if you compile a package on one instance it goes invalid on the other(s) (at least it did when we Oracle on site helping us out when we took our test system live into production) so I do not recommend you compile the package upon startup of the second instance.

A word of warning - watch my typing. I know what I ment so I overlook my typos
no matter how many times I read what I wrote unless maybe it is the next day.

Hope this helps.

Mark Powell -- No witty disclaimer.              Received on Mon Aug 26 1996 - 00:00:00 CEST