Re: Space shortage in SGA

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<ttt11tkoqar50e_at_corp.supernews.com>...
> "mpinzuti" <mpinzuti_at_virgilio.it> wrote in message
> news:3BDE39BA000003CE_at_ims2a.cp.tin.it...
> I launched a PL/SQL script made of about 3500 rows and after a while the
> DBMS prompted this:
> ORA-04031: unable to allocate 39296 bytes of shared memory ("unknown
> object","PL/SQL MPCODE","BAMIMA: Bam Buffer")
> I don't know whether Oracle has got a limited number of rows in a script
> to carry out but how can I solve my problem?
>
> Thanks in advance!!
>
> --
> Posted from vsmtp3.tin.it [212.216.176.223]
> via Mailgate.ORG Server - http://www.Mailgate.ORG
>
> This is a problem with the shared pool, where pl/sql code is compiled.
> A quick fix is to increase the shared_pool_size parameter in init.ora and
> bounce the database.
> This is no structural solution however.
>
>
> Hth

mpinzuti,
To add to what Sybrand provided. You can get the 04031 error due to shared pool fragmentation problems. Sometimes flushing the pool will allow the failing code to succeed, however; the first step is resolving shared pool errors is usally to use the dbms_shared_pool.keep procedure to pin all large and all heavily used user written packages into the pool at instance start-up and to also pin the in-use Oracle supplied packages (standard, dbms_standard, dbms_utility etc...). You can find this information in a support note on Metalink by searching on the 04031 error.

execute sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD','P');

According to support there are over 20 shared pool memory bug fixes in the 8.1.7.2 release compared to 8.1.7.0 so depending on your release level upgrading may be an option you need to consider. Also in our experience after upgrading from version 8 to 8.1 we had to add about 20% more space to the shared pool to run the same application without getting errors. How much of that was due to the bugs we had to get iTAR support with and how much just to 8.1 needing more space I do not know.

• Mark D Powell --