Archive for category Utilities

Data Pump and Streams Pool

Last week, while exporting a couple of tables for a BI project using the Data Pump utility, the following error occurred:

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SCOTT
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_20070604114151” and “KUPC$S_1_20070604114151” for Data Pump job
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPC$QUE_INT”, line 1580
ORA-00832: no streams pool created and cannot automatically create one

This database is not setup with the “Automatic Shared Memory Management” feature –> ASMM, all of the memory parameters are manually set.

You might wonder why ? Well, the reason why this database was not setup to use ASMM is that the database resides on a 32bits OS and uses RAMFS and HugePages to enable the use of 10GB of SGA. When using RAMFS and HugePages, it is required to manually set each memory parameter, I will discuss this feature in details next week.

Also, the « streams_pool_size” parameter was not setup since Oracle Streams is not used on this database, and I was surprised to see that the Data Pump utility needed a Streams Pool to actually work !

The Oracle Documentation explains the following Oracle Streams feature:Streams_pool_size Documentation“If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool”.

As we can see, Oracle did its homework when it comes to the use of Streams when no memory has been explicitly allocated (streams_pool_size). I am wondering why the Data Pump utility won’t work as per the same logic ? (if no streams_pool is setup, then have the Data Pump automatically grab the memory it needs from the shared_pool or the buffer_cache.) Maybe a new feature in 11g ??

Once the streams_pool_size parameter has been configured, the data pump worked just fine.

1 Comment