Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> ORA-04031 yet 40% of 'free memory' in V$SGASTAT

ORA-04031 yet 40% of 'free memory' in V$SGASTAT

From: Spendius <spendius_at_muchomail.com>
Date: 31 Mar 2005 05:49:41 -0800
Message-ID: <aba30b75.0503310549.3b1a17c8@posting.google.com>


Hi,
Here are the results of an attempt to connect to our DB a few minutes ago:
SQL> conn system_at_XXXXXXXXX
Enter password:
ERROR:
ORA-04031: unable to allocate %s bytes of shared memory ("%s","%s","%s","%s")

but AT THE SAME TIME from another session (as SYSTEM) I had gotten earlier I could see this:
select ss.*, round((ss.bytes/&nbytes)*100, 2) prop "FREE ROOM LEFT" from v$sgastat ss
where pool = 'shared pool'
  and name ='free memory'
order by 4;
POOL NAME BYTES FREE ROOM LEFT ----------- ----------- ------------ -------------- shared pool free memory 13929264 42.06

(with &nbytes being the SUM(bytes) FROM V$SGASTAT computed before in NOPRINT mode in the same SQL script) And I finally could connect when the "FREE ROOM LEFT" column above was reading... 17.02%.
What does the value of 'free memory' in this view really show ?

Thanks.

SQL> @version
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production
SQL> show sga
Total System Global Area 269486288 bytes

Fixed Size                   731344 bytes
Variable Size             184549376 bytes
Database Buffers           83886080 bytes
Redo Buffers                 319488 bytes
Received on Thu Mar 31 2005 - 07:49:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US