Propagation job started failing intermittantly with ORA-04031: unable to allocate bytes of shared m
A couple days ago my Oracle 10.2.0.4 propagation job started failing with the error ORA-04031: unable to allocate bytes of shared memory ("","","","").
I get the error for 16 times and the job fails. Attempts to restart it fail with the same error, but then a few hours later I am able to restart it and it runs fine for a few hours and then hits the same situation again. As far as I know nothing has changed recently in the environment (running on AIX) or application.
Note that the error doesn't indicate which memory pool is having the problem. I assumed it must be either the SHARED_POOL_SIZE or the STREAMS_POOL_SIZE, so I dynamically increased them from 912M to 2448M and from 800M to 2048M respectively. Note that I have SGA_TARGET and SGA_MAX_SIZE set so Oracle did automatically make some minor adjustments to db_cache_size shared_pool_size, but by querying the v$sga_resize_ops table I can see that the settings took effect. However this has not improved the situation and I'm still getting the same ORA-04031 errors.