Re: Increasing size of SGA / Reducing Use of SGA
Date: Tue, 15 Mar 94 10:04:33 -0500
Message-ID: <9403151004.PN09158_at_LL.MIT.EDU>
In article <1994Mar11.124410.978_at_gtewd.mtv.gtegsc.com> davidsonj_at_gtewd.mtv.gtegsc.com writes:
>From: davidsonj_at_gtewd.mtv.gtegsc.com
>Subject: Increasing size of SGA / Reducing Use of SGA
>Date: 11 Mar 94 12:44:09 -0800
>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.
Easy you are probably running Unix, just call your Unix/Risc vendor and ask them how to double the number of semaphores. To do this you must re-gen the Kernal...It is really no big deal. Semaphores are like pointers, or locks, that Unix memory uses. You must bring down the system, though to do this.
JF
>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 Tue Mar 15 1994 - 16:04:33 CET