Resize shared_pool / Avoid Ora-4030

Giganews Newsgroups
Subject: Resize shared_pool / Avoid Ora-4030
Posted by:  Stephan Bressler (
Date: Thu, 15 Jan 2004

Hi NG,

I'm thinking about avoiding ora-4030 errors due to many, many unique big
statements using the memory resizing feature of oracle 9i. Flushing the
shared pool usually is of no help.

Therefor I suggest the following procedure:
1. configure shared_pool_size initially to e.g. 300M
2. each night (or any other low-load-period),
    resize shared_pool_size to 100M
3. immediately after that, resize to 300M

This would give a 200M totally free, unfragmented shared_pool.

In theory! In praxis the "alter system set shared_pool_size=100M" lasts
for 1 hour and is still running. CKPT eats up 1 CPU.
I'm running on Linux.

Any ideas / suggestions?