| ORA-04031 shared memory errors are occuring [message #266359] |
Mon, 10 September 2007 11:39  |
aidi-h
Messages: 45
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  |
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 #266627 is a reply to message #266359] |
Tue, 11 September 2007 04:36 |
aidi-h
Messages: 45
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
