SGA_TARGET is zero, so of course a resize operation failed.
Please reset SGA_TARGET to equal SGA_MAX_SIZE and please consider
unsetting all of the other SGA parameters except SHARED_POOL_SIZE, to
allow automatic SGA management to do what it is supposed to do? Please
unset the underscore parameters too.
The days of micromanaging SGA components by guesses and wishes were over
decades ago.
On 6/15/2022 2:20 AM, Pap wrote:
We are hitting below Ora-4031 in one customer database. Even we have 'free memory' available (~2GB) in shared_pool during the error. And also the size of the "KKSSP" sub pool and it was showing null value only. Its version 19.11 database has the below memory setup. We do see the resize operation in which the shared_pool_size was 12GB and was GROWing and db_cache was 2GB and was tryin to SHRINK and both errored out.
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^3497","kglseshtTable")
memory_target - 0, memory_max_target - 0,sga_max_size - 35GB,sga_target - 0,shared_pool_size - 8GB,shared_pool_reserved_size - 1.2GB,_shared_pool_reserved_pct - 15,db_cache_size- 20GB