I am running an .plb file at SQL*Plus that contains over 70 wrapped procedures. I get the following error for a particular procedure.
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 391296 bytes (PLS non-lib hp,PAR.C:parchk:ptb)
The error does not occur if only the single procedure is created.
Database info: version 8.1.6 on Windows NT
db_block_size = 8192
sort_area_size = 65536
sort_area_retained_size = 65536
db_block_buffers = 2048
shared_pool_size = 15728640
large_pool_size = 614400
java_pool_size = 20971520
This is happening because your process memory is getting exhausted. The possible solutions would be
- Increase the ulimit ( virtual memory ) to unlimited.
% ulimit -a would give you the current settings.
- Decrease the SGA size, if it is set to a very large value. This is required if you are hitting the address space limits.
- You may also try decreasing the parameters like sort_area_size , bitmap_merge_area_size , create_bitmap_area_size .
- You can also free up the unused PGA memory by running the procedure dbms_session.free_unused_user_memory..
If your application uses lots of PL/SQL tables, it might be a good idea to initialize them to NULL when not used. This will force
the garbage collection and there by freeing up the process memory.
Are you using Java on Oracle? If no then why do you require such a big Java_pool_size? Set it to 0 and increase the shared_pool_size/large_pool_size.