Re: Why am I getting "Unable to allocate memory" errors?

From: Ken Denny <kennethd_at_nortel.com> >> Packaged procedures in my database are frequently failing with ORA-04031: unable to allocate nnnnn bytes of shared memory << and >> recently had my DBA increase the shared memory pool by 8 Meg m<<

1. The shared pool can still be too small if your shop is using a lot of packaged and stored procedures
2. The shared pool becomes fragmented when used so large objects can fail to find enough contiguous space for successful loading
3. There were numerous space management bugs with early releases of 7.2 (You failed to list your version)

Possible fixes
1) For all versions of 7.1, 7.2, and 7.3 (plus probably 8.0) use the sys.dbms_shared_pool.keep(package_name,'P') procedure call to pin all large packages into memory at database startup -- I recommend adding a dummy procedure to every package that can be executed to load the entire package into memory and to force automatic recompilation after object maintanence before application access. 2) With version 7.3 there are init.ora parameters that can be set to keep a minimum amount of shared pool available. They are detailed in the Reference manual along with the v$ tables.
3) In the meantime try 'alter system flush shared_pool;' then try again

Good Luck

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Wed Feb 25 1998 - 00:00:00 CST