Troubleshooting

EXP – IMP: ORA-04031

The ORA-04031 error returns whenever oracle attempts to allocate a large piece of contiguous memory in the shared pool but fails to allocate. Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request the ORA-04031 error is returned.

Since in 11g automatic memory management in enabled and this allows the dba to reserve a pool of shared memory that is used to allocate the shared pool, the buffer cache, the java pool and the large pool with the single parameter SGA_TARGET. So simply increase of SGA_TARGET likely solve the problem.

UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 16 bytes of shared memory (“shared pool”,”SELECT job_id FROM v$datapum…”,”SQLA”,”tmp”)
ORA-06512: at “SYS.KUPV$FT_INT”, line 2904
ORA-06512: at “SYS.KUPC$QUE_INT”, line 572
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3263
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4488
ORA-06512: at line 1

 

Solution
Let’s see the sga_target value.
SQL> show parameter sga_t
NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 64G

And SGA_MAX_SIZE value,
SQL> show parameter sga_max
NAME TYPE VALUE
———————————— ———– ——————————
sga_max_size big integer 64G

We see these two parameter settings are low. So we increase it and restart database. As setting of SGA_TARGET is depend on SGA_MAX_SIZE and to change SGA_MAX_SIZE we need to bounce the database in order to effect.
SQL> alter system set sga_max_size=64G scope=spfile;

System altered.

SQL> alter system set sga_target=100G scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1248768 bytes
Variable Size 96469504 bytes
Database Buffers 209715200 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.

SQL> show parameter sga_target

NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 100G

problem is solved.

Advertisements
Standard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s