Re: Memory Problems with PL/SQL Packages
Date: 1998/01/20
Message-ID: <6a39qu$91e$1_at_wyrm.its.uow.edu.au>#1/1
In article <6a202i$bkj_at_news.tuwien.ac.at> Christian Greissing, manitu_at_tick.cslab.tuwien.ac.at writes:
>So after some time of use of this package i get the failure
>that no more shared memory is available (sorry haven't the right Error #
>by hand).
The dreaded ORA-4031 message!
This is usually caused by your shared pool becoming fragmented, so that when the RDBMS tries to load your package, it can't find a chunk of free memory large enough. It affects all versions of Oracle up to 7.3 (and maybe 8 - I haven't used it, so I can't say either way). Versions 7.1 and 7.2 were more prone to this than 7.3, which had improved memory management.
- Immediate fix: flush your shared pool. From sql*dba or svrmgrl, execute:
alter system flush shared_pool;
This will allow your package to run, but will degrade performance temporarily. It should only be done if you absolutely have to.
2. Pin large packages in memory at RDBMS startup. This is usually a good policy - pinned packages won't get aged out of the shared pool, reducing fragmentation. The dbms_shared_pool package has tools to do this.
3. Enlarge your shared pool by bumping up SHARED_POOL_SIZE, provided that you have enough memory.
Copyright 1998 Phil Herring. This article may not be reproduced for profit.
Received on Tue Jan 20 1998 - 00:00:00 CET