Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_ POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_ POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.
Try reducing the SORT_AREA_SIZE = 100MB SORT_AREA_RETAINED_SIZE = 50 MB and give 100MB to SHARED_POOL_SIZE and then try, hope you could come over the problem.
Stand up for your principles even if you stand alone!
I also looked at metalink and found out that the problem is
basically due to memory management of large pool which doesn't
use an LRU mechanism.
One way of solving is to increase large pool so did that by increasing from 250 to 300 M.
Will keep monitoring to check if it is enough.
One point to discuss is large_pool_min_alloc
I understand that it is no more required in 8i as large pool
is allocated in chunks of 64 K by default.
The biggest problem is not being able to dynamically alter
sga + large pool in 8i. We will be upgrading to 9i in about
a month but until then are stuck with having to reboot the system / bounce the database to apply init.ora changes.
Our database is running in MTS configuration with
following 10 dispatchers & 20 servers.
There you are
It's your sort_area_size and hash_area_size using your large pool. You have to 2 options. Either to increase your large_pool_size or change back to dedicated server configuration. Whar are your sort area and hash area sizes? what is your max_roles_enabled? and what is the your db_files parameter set for?