Pavan DBA's Blog

The DBA Knowledge Store

expdp throwing ORA-04031 (stream pool) error

Posted by Pavan DBA on December 4, 2013


A week back I have faced an error related to datapump export and would like to present my findings here.

When I executed datapump export (expdp) job on a 10.2.0.4 database, it got failed with below error message

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYS
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 600
ORA-39080: failed to create queues “KUPC$C_1_20070823095248” and “KUPC$S_1_20070823095248” for Data Pump job ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPC$QUE_INT”, line 1580
ORA-04031: unable to allocate 4194344 bytes of shared memory (“streams pool”,”unknown object”,”streams pool”,”fixed allocation callback

when we look at last line, like me, many people wonder why it is showing stream pool here and what is the relation between stream pool and expdp?

Here is the background…

Datapump jobs (either expdp/impdp), when initiated, will use buffer queues in order to transfer the data directly from the master table to the dump file. Instead of creating new buffer queue, datapump operations will try to use the existing queues in stream pool memory area.

Normally, Stream pool will have buffer queues in order to store messages in memory that supports capture processes, apply processes, XStream outbound servers, and XStream inbound servers.

Ideally, Stream pool size will be managed (increase/decrease) automatically when ASMM or AMM is enabled. But, sometimes with this automatic memory management, it might happen that STREAM_POOL_SIZE has not been set and pointing to a size of 0. This is the cause for the error mentioned above.

Solution:

Set STREAM_POOL_SIZE parameter to a value greater than 0. Usually 48m should be the min size and this value will be more if there is a large database or a database with high work load.

So, friends, beware of Stream pool when doing expdp/impdp 🙂

 

Advertisements

3 Responses to “expdp throwing ORA-04031 (stream pool) error”

  1. Tanveer Malik said

    Thanks for sharing ….

  2. Ashish Kumar Mahanta said

    Even I was wondering !!! While doing expdp, got the same error. Same steps i used to move forward. Stream_pool_size changed it from 0 to 40M. By your excellent answer, my doubt is clear now. Thanks for sharing.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: