Increasing size of SGA / Reducing Use of SGA

From: <davidsonj_at_gtewd.mtv.gtegsc.com>
Date: 11 Mar 94 12:44:09 -0800
Message-ID: <1994Mar11.124410.978_at_gtewd.mtv.gtegsc.com>


I should have posted this a week ago considering how responsive Oracle tech support has been (I logged a TAR a week ago, have called back 3 or 4 times, and have yet to have anyone call back).

I'm willing to bet that the net is a least as responsive as #*&^%(^&(^*^&$$^&%
(expletives deleted) ORACLE. Anyway, here's the problem........

I (and many of my co-developers) have been running into the following error while we have been trying to create stored procedures (and in some cases to execute stored procedures):

create or replace package body SOME_NAME_HERE as *
ERROR at line 1:
ORA-04031: out of shared memory when trying to allocate 25868 bytes
(PL/SQL MPCODE,BAMIMA: Bam Buffer)

Looking at the description of the error message in the appropriate Oracle manual indicates that we are out of SGA (System/Shared Global Area) -- i.e. memory allocated to ORACLE -- and that we can either attempt to free up some of that memory or to increase the size of the memory allocated to the SGA by modifying our INITsid.ORA file.

I looked at our INIT.ORA file (the one for our instance of the database and not the template provided by Oracle) and saw that ORACLE provided some default parameter values for SMALL, MEDIUM, and LARGE database and that we were currently using the parameter settings for the SMALL database. I modified the INIT.ORA file to use the MEDIUM settings, shut down ORACLE, and restarted it using dbstart. When I did this, I got the following messages:

SQLDBA> Connected.
SQLDBA> ORA-07252: spcre: semget error, could not allocate semaphores. ULTRIX Error: 28: No space left on device Additional Information: 1
SQLDBA>
SQL*DBA complete.

Database "sid" warm started.

I have no idea why I am getting this error and have been forced to revert to the original INIT.ORA file in order to continue development (although significantly impeded by the original error).

Two questions:
a) Why am I unable to restart the database with the new INIT.ORA file when

   I seem to have plenty of memory and diskspace? According to the prologue    of the INIT.ORA file, I only need 6.8 MB - we have 164 MB or so - and    I don't think changing this file has much if any impact on disk space.

b) What sort of things can I do to free up SGA? I tried dropping MANY packages

   and tables from various user accounts that were not currently in use and    this seemed to have no impact when I thought reducing the size of the    data dictionary would reduce the use of the SGA. Can anyone explain    this? Suggest how to reduce my use of SGA?

MUCH THANKS IN ADVANCE...Let's see if you can beat ORACLE to the solution even when they have a 1 week head start!

           jim

p.s. I am running version 7.0.12 or the Oracle server on Ultrix 4.2A Received on Fri Mar 11 1994 - 21:44:09 CET

Original text of this message