Running on Oracle 12.1.0.2 AIX Power9 (yes, I know it's 2021, but that's the customer I've got)
Executing a plsql block that includes 1000 inserts as plain text and commit in the end.
begin
insert into tab1 values (1);
insert into tab1 values (2);
...-- 998 more inserts
commit;
end;
It fails consistently on ORA-04030: out of process memory when trying to allocate 20504 bytes (callheap, KTI call freeable small pool)
The same plsql block runs successfully on hundreds of other databases, including this very version and OS.
The database was rebooted, no sessions except of mine.
The memory parameters are:
*.db_16k_cache_size=0
*.db_block_size=8192
*.db_cache_size=30g
*.java_pool_size=200m
*.large_pool_size=1g
*.lock_sga=TRUE
*.pga_aggregate_target=10g
*.shared_pool_size=8g
I have tried to use internal parameters – no luck, same error
*._use_realfree_heap=TRUE
*._realfree_heap_pagesize = 262144
Changed _use_realfree_heap=FALSE – same error.
The trace file generated claims that the process consumes 95MB only.
Best Answer
That alone deserves an upvote. I truly loathe this platform.
Hitting that error with such low amount of allocated memory is rather an OS configuration issue and not a database configuration issue.
Check the limits configured and change them if needed:
Configuring Shell Limits and System Configuration Parameters for AIX