Home » RDBMS Server » Server Administration » ORA-04031 shared memory errors are occuring
ORA-04031 shared memory errors are occuring [message #266359] Mon, 10 September 2007 11:39 Go to next message
aidi-h
Messages: 44
Registered: November 2005
Member
Hello all, I am getting the following error and the DB is failing,
"ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 16 bytes of shared memory ("shared pool","select count(*) from sys.job...","sql area","kglhin: temp")"

Can anyone put me in the right direction on how to stop this error from occuring.

I have run the following if this helps,
"select pool,name,bytes/1048576 "Size in MB" from v$sgastat where name = 'free memory';

POOL NAME Size in MB
------------ -------------------------- ----------
shared pool free memory .877067566
large pool free memory 3.13963318
java pool free memory 4


select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

POOL MBYTES
------------ ----------
51.9977913
java pool 4
shared pool 93.0160789
large pool 4"
Re: ORA-04031 shared memory errors are occuring [message #266364 is a reply to message #266359] Mon, 10 September 2007 11:47 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
ORA-04031:	unable to allocate string bytes of shared memory ("string","string","string","string")
Cause:	More shared memory is needed than was allocated in the shared pool.
Action:	If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages,
 reduce your use of shared memory, or increase the amount of available shared memory by increasing 
the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool
 is out of memory, increase the INIT.ORA parameter "large_pool_size".
Re: ORA-04031 shared memory errors are occuring [message #266365 is a reply to message #266359] Mon, 10 September 2007 11:49 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
you dont have a big enough shared pool, increase sga_target
93MB is not that large
Re: ORA-04031 shared memory errors are occuring [message #266433 is a reply to message #266359] Mon, 10 September 2007 22:02 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Set simply sga_target=0!!!
Re: ORA-04031 shared memory errors are occuring [message #266450 is a reply to message #266433] Mon, 10 September 2007 23:22 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Set simply sga_target=0!!!
I think you want to say "sga_target >0" Smile


Regards
Taj
Re: ORA-04031 shared memory errors are occuring [message #266459 is a reply to message #266359] Mon, 10 September 2007 23:37 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I am pretty much sure the questioner keep the autotuning disabled.
So , enable it. If you want then

just run a query

 SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
 FROM DUAL
;

And then
 ALTER SYSTEM SET SGA_TARGET=result of previous query


Note that you also make other automatically sized SGA components (like shared_pool_size,java_pool_size to 0)

After all restart your database if you set by alter system.
Re: ORA-04031 shared memory errors are occuring [message #266627 is a reply to message #266359] Tue, 11 September 2007 04:36 Go to previous messageGo to next message
aidi-h
Messages: 44
Registered: November 2005
Member
Many thanks for your response.
I have one query in regard to the sga_target setting.

I ran arju's query
1 SELECT (
2 (SELECT SUM(value) FROM V$SGA) -
3 (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
4 ) "SGA_TARGET"
5* FROM DUAL
SQL> /

SGA_TARGET
----------
167772160

I then ran
SQL> show parameter sga_target;

NAME TYPE VALUE
------------------------------------ ----------- -----------
sga_target big integer 160M

These are almost the same obviously.

Why then do I get 93M in the following shared pool query
SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

POOL MBYTES
------------ ----------
47.9977913
java pool 4
shared pool 97.016964
large pool 4

Any help would be appreciated.

Re: ORA-04031 shared memory errors are occuring [message #266629 is a reply to message #266359] Tue, 11 September 2007 04:39 Go to previous message
aidi-h
Messages: 44
Registered: November 2005
Member
Regarding my last posting. Is the reason for the problem the 'blank' pool that takes up 47M?
Previous Topic: Killing a process
Next Topic: very urgent please help me error (merged)
Goto Forum:
  


Current Time: Sun Dec 04 21:02:17 CST 2016

Total time taken to generate the page: 0.08240 seconds