CODEWITHSUNDEEP
oracle11goracle-xedatapump
Bishan
Bishan

Reputation: 15702

Unable to allocate x bytes of shared memory

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?

Upvotes: 0

Views: 13615

Answers (3)

unimatrix10
unimatrix10

Reputation: 31

If you are using ASMM (Automatic Shared Memory Management) increase the "SGA_TARGET" to match to whit the "SGA_MAX_SIZE" parameter.

alter system set SGA_MAX_SIZE=8914M scope=spfile;

alter system set SGA_TARGET=8914M scope=spfile;

If you are using AMM (Automatic Memory Management) increase the "MEMORY_TARGET" to match to whit the "MEMORY_MAX_TARGET" parameter.

alter system set MEMORY_MAX_TARGET=2G scope=spfile;

alter system set MEMORY_TARGET=2G scope=spfile;

Then restart the database.

And also is a good idea to check the value of "STREAMS_POOL_SIZE"

show parameter streams_pool;

select * from v$sgainfo;

Source: https://support .oracle.com/knowledge/Oracle%20Database%20Products/1907256_1.html

Happy Coding;

Upvotes: 0

Etay Gudai 972-522-3322-47
Etay Gudai 972-522-3322-47

Reputation: 717

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

Upvotes: 1

GWu
GWu

Reputation: 2787

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.

Upvotes: 1

Related Questions