Re: ORA-04031 shared memory

On 5 Apr 2004 12:59:38 -0700, lrebyc_at_yahoo.de (Lars) wrote:

>Hi!
>
>Currently I am facing a ORA-04031 on a Oracle 8:
>
>ORA-04031: unable to allocate 4200 bytes of shared memory ("shared
>pool","SEQ$","sga heap","state objects")
> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
>
>I checked a lot of hints about configuration of the shared pool.
>Looking
>at the values raises the question, why 04031 comes up?
>
>SQL> select pool, name,
> sgasize/1024/1024 "Allocated (M)",
> bytes/1024 "Free (K)",
> round(bytes/sgasize*100, 2) "% Free"
>from (select sum(bytes) sgasize from sys.v_$sgastat) s,
>sys.v_$sgastat f
>where f.name = 'free memory';
>
>POOL NAME Allocated (M) Free (K) %
>Free
>----------- -------------------------- ------------- ----------
>----------
>shared pool free memory 226.808434 38591.6406
>16.62
>large pool free memory 226.808434 600
>.26
>java pool free memory 226.808434 15728
>6.77
>
>Large pool should make problems, not the shared segmen?
>
>Any hints for this problem? Thanks in advance,
>
>Lars

Typical case of shared pool fragmentation. The requested 4200 bytes are available, but not contiguously.
There is a note up on Metalink with a query to determine whether increasing shared_pool_reserved_siz_min_alloc will help. Guess you will end up increasing the shared pool. Shared pool fragmentation is usually caused by not using bind variables in sql statements.

--
Sybrand Bakker, Senior Oracle DBA