java.sql.SQLException: ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","AWAPI3","PL/SQL MPCODE","BAMIMA: Ba
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "APPWORX6.AWAPI2", line 367
ORA-06512: at "APPWORX6.AW_WEB_API", line 949
ORA-06512: at line 1
It looks to me that I have to increase the shared_pool_size. Here is what I have in the parameter file.
I run a script to find out the dictionary cache ratio and it is 3.18%
What I want to know is
1. how can I determine by how much to increase the shared_pool_size.
2. How can I increase it. Is it simply by changing the value in the parameter file.
3. do I have to shutdown the DB and restart it?
1. This depends on your application code. I would take previous postings advice and keep an eye on usage of shared pool. You can always try flushing the shared pool as well "alter system flush shared_pool"
2. Yes it is simple as changing the parameter in the init file.
3. Yes you must restart your instance.
You can keep an eye on the free memory in the Shared Pool by running this:
select bytes from v$sgastat
where pool = 'shared pool'
and name = 'free memory';
BTW, I had this same problem, caused by one procedure that ate up the shared pool memory; by running this command every so often as the procedure was running I was able to see the shared pool free memory being used up.
90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.
you can change shared_pool_size on the fly in 9i using alter system ... scope=both (probably in 8i aswell) but you are limited by sga_max_size which you cant change dynamically (need to bounce the database).