error regarding variable portion of SGA

error regarding variable portion of SGA

Post by brad winter » Wed, 04 Oct 1995 04:00:00

I recently wanted to increase the init.ora parameter shared_pool_size
from 6M to 8M.  On startup I quickly get an error "cannot allocate
variable portion of SGA".
I was able to increase to 7M, but not 8M+.  I can't find much on
"variable portion of SGA" documented.  Anyone have any ideas?.  Thanks in

  Brad Winters


error regarding variable portion of SGA

Post by Frank Bommarit » Wed, 11 Oct 1995 04:00:00

This error is related to the configuration of your Unix Kernel.

Oracle needs contiguous memory for the variable portion of the
SGA.  Your system is probably not defined to have large chunks of
contiguous memory.  I would refer you to the Oracle for xxx
Installation and Configuration Guide.  Pay special attention to
the OS memory congfiguration.

I hope this helps.



1. Increasing size of SGA / Reducing Use of SGA

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

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
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!


p.s.  I am running version 7.0.12 or the Oracle server on Ultrix 4.2A

2. where to buy the docs of MPEG standards

3. Help regarding Forms error.

4. Help with ATC/Addons

5. smsget: shmat error when trying to attach sga

6. Ringing Nuisance

7. ORA-04031 SGA error!! HELP!!

8. link to the video of the girl and the robot dancing

9. In regards to WebSystem error for 1K text writing

10. ORA-04031 SGA error !! HELP!!

11. Question About Viewing Portion of LONG Value in SQL*Plus

12. Q: How to use only a portion of a LONG?

13. PL/SQL error with variables