Re: Error ORA-04031: unable to allocate 288 bytes of shared memory
From: Denis M. Goddard <d.m.goddard_at_cummins.com>
Date: 1997/01/07
Message-ID: <32D2B8E6.596C_at_cummins.com>#1/1
Date: 1997/01/07
Message-ID: <32D2B8E6.596C_at_cummins.com>#1/1
Vijay Vardhineni wrote:
>
> Make sure that your database has a large SGA. If possible, convert your
> code into a pl/sql package, and PIN it in the SGA.
I recently had this type of problem (not enough RAM in the Shared pool). To be very explicit about the fix:
- Use Server Manager (Or SQL*DBA) and connect INTERNAL (as user 'SYS')
- Run the script $ORACLE_HOME/rdbms/admin/dbmspool.sql
- Convert your PL/SQL program into a package (See the PL/SQL User's Guide & Reference for details on Packages)
- Execute the following command from Server Manager (or SQL*DBA) *every time* the database is restarted:
SVRMGRL> BEGIN
DBMS_SHARED_POOL.KEEP ('my_package_name');
END;
You can check that the PL/SQL package is "really" pinned in memory:
SET SERVEROUTPUT ON;
BEGIN
DBMS_SHARED_POOL.SIZES(1);
END;
You should see your package scroll by, flagged as 'Pinned'.
If there are too many lines of output increase the argument to the SIZES command:
DBMS_SHARED_POOL.SIZES(10); -- shows everything bigger than 10KB
Good luck..... and buy more RAM!
-Denis Received on Tue Jan 07 1997 - 00:00:00 CET