Automated Maintenance Tasks

This section looks at the automated maintenance tasks in the realm of memory management. In earlier releases of Oracle, when you got an ORA-04031 error related to memory, you had to resort to several methods to find the true cause and rectify it. One of the methods was to create an event in the initSID.ora file to get a trace file with additional information about the problem. Starting with Oracle 9.2.0.5 and Oracle Database 10g releases, a trace file is created by default in the user_dump_dest directory whenever a memory error occurs. So if you still get errors, even with a sufficiently large memory and with automatic memory management turned on, Oracle has already created the trace files for you to be sent to Oracle support for analysis.

Some errors with ORA-04031 code may not be the result of insufficient SGA. If you run out of memory while compiling a Java code in an application (within loadjava or deployejb), you will get a SQL exception error with ORA-04031 code like this:

[View full width]
 A SQL exception occurred while compiling: ORA-04031: unable to allocate bytes of  shared  memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal") 

In this scenario, the error is not related to insufficient SGA, but is instead caused by a small value for JAVA_POOL_SIZE. When you get this error, the only solution is to shut down the database and increase the JAVA_POOL_SIZE setting to a larger value. Restart the database, and try the same command again.