| ORA-04031 (Urgent) [message #59539] |
Tue, 02 December 2003 05:21  |
kothari Alpesh
Messages: 27
Registered: December 2001
|
Junior Member |
|
|
Dear All,
I am using oracle 8.1.7.0.0 on solaris (24*7) and i have got an error once/ twice in a day. So. What is the wrong with my DB. How can i able to know which object req. more space??? I knew the solution of increase the size of shared pool. but is the any other way to work around this error?? Pls......
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","unknown object","sga heap","state objects")
Thanks In advance...
-Alpesh
|
|
|
|
| Re: ORA-04031 (Urgent) [message #59540 is a reply to message #59539] |
Tue, 02 December 2003 06:20  |
Adrian Billington
Messages: 139
Registered: December 2002
|
Senior Member |
|
|
Usually when I see this error it makes me think that an app is not using bind variables and hence the shared pool becomes very fragmented.
However, a quick look at Metalink shows the following potential reason:-
quote Oracle support...
<pre style="font-family: Courier New, Monospace; color: Blue; font-size: 9pt">
It is likely you are encountering bug 1397603 which effects 8.1.7. The workaround is to set the following init.ora parameter to zero:
_db_handles_cached = 0
</pre>
...unquote Oracle support
Might be worth checking the bug out but also having a closer look at your sqlarea for SQL not using bind variables.
Regards
Adrian
|
|
|
|
| Re: ORA-04031 (Urgent) [message #59542 is a reply to message #59539] |
Tue, 02 December 2003 09:02 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
In addition to what Adrian mentioned earlier, also make sure you configure large pool if you are using Shared servers(MTS),RMAN,Parallel query servers to prevent these guys using(possibly fragmenting) shared pool.
Increasing shared_pool_size may or may not be a solution,depending on conditions. Also you could try pinning your frequently used large packages/procedures etc using DBMS_SHARED_POOL.KEEP , although it may not be of much help , since the introduction of large pool and LRU algorithm of the shared poool, but you could try neverthless to see if it helps you , in your case.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
