How to handle the error: ORA-04031: unable to allocate 48 bytes of shared memory

This is an example the error message:

Complete Text of Error Messages:
Error processing data in upload.ab4; ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "PBDS.ADU_BASE", line 490
ORA-06512: at "PBDS.ADU_BATCH", line 1696
ORA-04031: unable to allocate 48 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","sql area","qeSel: qkxrXformSel")
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STANDARD"
This error can occur at times of high database activity. Retrying the process will usually work. If the database encounters this error regularly, then SGA settings (see below for explanation) might need to be changed.

Oracle SGA Concepts - Explanation of SGA

The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle instance (an instance is your database programs and RAM).

All Oracle processes use the SGA to hold information. The SGA is used to store incoming data (the data buffers as defined by the db_cache_size parameter), and internal control information that is needed by the database. You control the amount of memory to be allocated to the SGA by setting some of the Oracle initialization parameters.  These might include db_cache_size, shared_pool_size and log_buffer.

In Oracle Database 10g you only need to define two parameters (sga_target and sga_max_size) to configure your SGA. If these parameters are configured, Oracle will calculate how much memory to allocate to the different areas of the SGA using a feature called Automatic Memory Management (AMM). As you gain experience you may want to manually allocate memory to each individual area of the SGA with the initialization parameters.

We have already noted that the SGA was sub-divided into several memory structures that each have different missions. The main areas contained in the SGA that you will be initially interested in have complicated names, but are actually quite simple:

* The buffer cache (db_cache_size)
* The shared pool (shared_pool_size)
* The redo log buffer (log_buffer)

Note:  AMM and dynamic Oracle memory management have measurable overhead.

Environment

 Team Approach
 RSR

Was this article helpful?