Home » RDBMS Server » Server Administration » unable to allocate 4096 bytes of shared memory
unable to allocate 4096 bytes of shared memory [message #199630] Wed, 25 October 2006 09:43 Go to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Don't know where to post... so posted under Server Administration.


What's could be wrong here.
Using Oracle 10g R2.
Do I need to change my memory settings..


ORA-22303: type "SYS"."AQ$_HISTORY" not found < ORA-04031: unable to allocate 4000 bytes of shared memory ("shared pool","select name,password,datats#...","sql area","seg:kggfaAllocSeg") < ORA-22303: type "SYS"."AQ$_HISTORY" not found < ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","sql area","kgghteInit") < ORA-22303: type "SYS"."AQ$_HISTORY" not found < ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","sql area","kgghteInit") < ORA-22303: type "SYS"."AQ$_HISTORY" not found < ORA-04031: unable to allocate 4000 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","sql area","seg:kggfaAllocSeg") < ORA-22303: type "SYS"."AQ$_HISTORY" not found < ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","sql area","kgghteInit") < ORA-22303: type "SYS"."AQ$_HISTORY" not found < ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","sql area","kgghteInit")

[Updated on: Wed, 25 October 2006 09:50] by Moderator

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199632 is a reply to message #199630] Wed, 25 October 2006 09:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What is the value for parameterr cursor_space_for_time in your database?
Re: unable to allocate 4096 bytes of shared memory [message #199633 is a reply to message #199630] Wed, 25 October 2006 09:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
This is default.
SQL> show parameter cursor_space

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_space_for_time                boolean     FALSE

Is that changed in your case?
Re: unable to allocate 4096 bytes of shared memory [message #199634 is a reply to message #199630] Wed, 25 October 2006 09:57 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
YES
Re: unable to allocate 4096 bytes of shared memory [message #199635 is a reply to message #199634] Wed, 25 October 2006 09:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If it is set to TRUE, change it to false and bounce the database.
Re: unable to allocate 4096 bytes of shared memory [message #199636 is a reply to message #199630] Wed, 25 October 2006 10:00 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
it is set to FALSE which is default
Re: unable to allocate 4096 bytes of shared memory [message #199637 is a reply to message #199630] Wed, 25 October 2006 10:02 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member

these are my current settings

db_cache_size=500M
shared_pool_size = 250M
large_pool_size = 140M
#java_pool_size = 8M


Re: unable to allocate 4096 bytes of shared memory [message #199638 is a reply to message #199636] Wed, 25 October 2006 10:02 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Default is "False"

Re: unable to allocate 4096 bytes of shared memory [message #199640 is a reply to message #199636] Wed, 25 October 2006 10:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please post the case history.
Is this a reproducible case?
Under what circumstances are you getting this? or What were you doing?
Is there a related trace file in alertsid.ora?
if so post it .
Re: unable to allocate 4096 bytes of shared memory [message #199646 is a reply to message #199630] Wed, 25 October 2006 10:23 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
this is a production database.
This is a first occurance so far...
it started at 2 hour ago...
Database is being used for a normal production purpose. Users are submiiting queries as usual. There is nothing abormal noticed this morning. This database is only a read only database. trace file is produced... summerizing in all it tells the below.

*** 2006-10-25 11:18:54.282
SMON: following errors trapped and ignored:
ORA-04031: unable to allocate 4160 bytes of shared memory ("shared pool","MON_MODS$","sga heap(1,0)","kg
lsim heap")
*** 2006-10-25 11:19:04.359

I haven't seen this until this morning.
Any help is greatly appreciated
Re: unable to allocate 4096 bytes of shared memory [message #199647 is a reply to message #199646] Wed, 25 October 2006 10:28 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
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: unable to allocate 4096 bytes of shared memory [message #199648 is a reply to message #199646] Wed, 25 October 2006 10:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Ok.
The generic solution is to increase the shared pool.

But it actually depends. 'Certain versions' have 'certain bugs' with 'certain options' used. ( That is very clear!. Right?. I cannot be any less vague Smile)

[Updated on: Wed, 25 October 2006 10:32]

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199651 is a reply to message #199630] Wed, 25 October 2006 10:36 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Thanks for the immediate response. and following up on this
The immediate action I am taking is to double the shared_memory_pool. but cause need to be investigated.

could it be a space problem in system or sysaux... just wondering...

there is no change to the database in past 10 months or so except the data is growing periodically. Oh!, wanted to correct about the database version. this database is on Oracle 10g R1 (not R2) Still remains to be a mystry at this time.


Re: unable to allocate 4096 bytes of shared memory [message #199655 is a reply to message #199651] Wed, 25 October 2006 10:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Bounce the database for now.
if the case is reproducible, you need to trace the user and statmement that is causing this.
Re: unable to allocate 4096 bytes of shared memory [message #199656 is a reply to message #199630] Wed, 25 October 2006 10:41 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Thanks so much Maahesh.
Appreciate your prompt help.

Warm regards
Re: unable to allocate 4096 bytes of shared memory [message #201086 is a reply to message #199630] Thu, 02 November 2006 08:20 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Usually ORA-04031 is caused by shared pool fragmentation.
2 main reasons are:
1. Shared Pool is too small (must be increased).
2. Application is generating SQL statements without bind variables.

Check both possibilities.
Previous Topic: CUSTOMIZED AUDITING
Next Topic: RAC instance name - Can i choose my OWN?
Goto Forum:
  


Current Time: Tue Dec 06 10:13:40 CST 2016

Total time taken to generate the page: 0.11693 seconds