Unable to allocate x bytes of shared memory

Question

When I'm trying to backup my oracle database, I got below error.

UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 16 bytes of shared memory ("shared pool","SELECT j
ob_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 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

How could I resolve this?

Answer 1

You are running out of memory in the shared pool part of the SGA.

As you seem to use XE 11g I assume you are using Automatic Memory Management. So I would first try to increase initialization parameter MEMORY_TARGET (and MEMORY_MAX_TARGET if exceeded by new MEMORY_TARGET).

Note you are limited to max 1GB in XE (see http://docs.oracle.com/cd/E17781_01/install.112/e18802/toc.htm#BABHJHHC).

To change it execute as SYS: alter system set MEMORY_TARGET=1GB;

For changing MEMORY_MAX_TARGET execute with additional parameter scope=pfile, and you will have to restart the instance.

If this doesn't help (max of 1GB already set): disable AMM partly and set initialization parameter shared_pool_size to say 250MB (or more) to enlarge the shared pool (at cost of the other components, buffer cache, ...) and try again.

Too see the current allocation of memory to pools in the SGA use

select nvl(pool,name) pool
      ,sum(bytes)/1024/1024 MB 
  from v$sgastat 
 group by nvl(pool,name)
;

If shared pool is already the largest and you are already at the limit of 1GB for memory_target then I fear you are out of luck with XE and should think about Standard Edition.

Answer 2

Following GWu Answer,

Here are the commands that saved my day for Oracle Express 11.2 ( you may change parameters values of course )

ALTER SYSTEM SET MEMORY_MAX_TARGET=1000m SCOPE=SPFILE;

ALTER SYSTEM SET MEMORY_TARGET=1000m SCOPE=SPFILE;

SHUTDOWN IMMEDIATE

STARTUP