OraFAQ Forum: Server Administration » unable to allocate 4096 bytes of shared memory

Home » RDBMS Server » Server Administration » unable to allocate 4096 bytes of shared memory

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

unable to allocate 4096 bytes of shared memory [message #199630]

Wed, 25 October 2006 09:43

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

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

What is the value for parameterr cursor_space_for_time in your database?

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199633 is a reply to message #199630]

Wed, 25 October 2006 09:55

Mahesh Rajendran
Messages: 10707
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?

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199634 is a reply to message #199630]

Wed, 25 October 2006 09:57

pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville

Senior Member

YES

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199635 is a reply to message #199634]

Wed, 25 October 2006 09:59

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

If it is set to TRUE, change it to false and bounce the database.

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199636 is a reply to message #199630]

Wed, 25 October 2006 10:00

pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville

Senior Member

it is set to FALSE which is default

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199637 is a reply to message #199630]

Wed, 25 October 2006 10:02

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

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199638 is a reply to message #199636]

Wed, 25 October 2006 10:02

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Default is "False"

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199640 is a reply to message #199636]

Wed, 25 October 2006 10:03

Mahesh Rajendran
Messages: 10707
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 .

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199646 is a reply to message #199630]

Wed, 25 October 2006 10:23

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

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199647 is a reply to message #199646]

Wed, 25 October 2006 10:28

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".

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199648 is a reply to message #199646]

Wed, 25 October 2006 10:30

Mahesh Rajendran
Messages: 10707
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

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.

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199655 is a reply to message #199651]

Wed, 25 October 2006 10:39

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #199656 is a reply to message #199630]

Wed, 25 October 2006 10:41

pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville

Senior Member

Thanks so much Maahesh.
Appreciate your prompt help.

Warm regards

Report message to a moderator

Re: unable to allocate 4096 bytes of shared memory [message #201086 is a reply to message #199630]

Thu, 02 November 2006 08:20

michael_bialik
Messages: 621
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.

Report message to a moderator

Previous Topic:	CUSTOMIZED AUDITING
Next Topic:	RAC instance name - Can i choose my OWN?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:43:10 CDT 2024