Re: ORA-4031/Shared Pool

From: Vijay Vardhineni <vardhineni_at_worldnet.att.net>
Date: 1997/01/03
Message-ID: <01bbf906$81e96fe0$1e6893cf@default>#1/1

You are correct. You need to execute
DBMS_SHARED_POOL.KEEP(your_package) everytime you startup your database. If you want to solve this problem immediately, you need to use the command

ALTER SYSTEM FLUSH SHARED_POOL; If the size of your package is big, then try splitting your package into several packages, so that only the packages that contain the procedures you are calling will be in SGA.

-- 
Vijaya Kumar Vardhineni
Oracle DBA, EDS
Plano, TX

David Kaiser <davidk_at_sfmic.com> wrote in article
<32CC2F16.4950_at_sfmic.com>...


> We have been wrestling with this error for a while.  I fully understand

> why we get this error (usually when trying to run larger packages).

> 

> Does anyone have a decent strategy for working with this problem

> (ORA-4031)?

> 

> We are running 7.2.2.3 with a shared pool of 18 MB.  We are getting the

> error because of fragmentation.

> 

> We have started to experiment with DBMS_SHARED_POOL.KEEP().  Does this

> procedure need to be called everytime the database is started?  Also has

> anyone had luck using SHARED_POOL_RESERVED_SIZE and

> SHARED_POOL_RESERVED_MIN_ALLOC in their init files to solve this.  (We

> have found these to be a bit buggy in their implementation).

> 

> Thanks for any ideas.

> -- 

> 

> +---------------------------------------+-------------------------------+

> | David Kaiser                          | e-mail: davidk_at_sfmic.com     

> |

> | Project Manager                       |    WWW: http://www.sfmic.com 

> |

> | State Fund Mutual Insurance Company   |                              

> |

> | 7500 Flying Cloud Drive, Suite 900    |  voice: 612.914.1317         

> |

> | Eden Prairie, MN 55344  USA           |    fax: 612.944.1169         

> |

> +---------------------------------------+-------------------------------+

>

Received on Fri Jan 03 1997 - 00:00:00 CST