Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> ORA-04031 yet 40% of 'free memory' in V$SGASTAT
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 bytesReceived on Thu Mar 31 2005 - 07:49:41 CST