Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at 10:28:18
从错误信息中可以看到ORA-04031: unable to allocate 2072 bytes of shared memory ("streams pool","unknown object","streams pool","kodpaih3 image")
从字面上理解是在给streams pool分配内存时出错造成的,MOS上有一篇文件档
DataPump Export (EXPDP) Fails With Error ORA-4031 ("streams pool", ...) (文档 ID 457724.1)
In this Document
Symptoms
Cause
Solution
References
--------------------------------------------------------------------------------
Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 16-MAY-2012***
Symptoms
DataPump export (EXPDP) reports the following errors:
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYSTEM
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_20070
823095248" 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
Cause
The problem seems initially caused by having set the STREAMS_POOL_SIZE instance parameter to 0.
The first argument of the ORA-4031 error message also indicates a problem with the Streams pool.
The streams pool is used exclusively by Oracle Streams, see http://docs.oracle.com/cd/E11882_01/server.112/e25789/memory.htm#CNCPT1235
Also, Data Pump export and import operations initialize the Oracle Streams pool because these operations use buffered queues.
For information about the streams pool, refer to http://docs.oracle.com/cd/E11882_01/server.112/e10705/prep_rep.htm#STREP202
The size of the streams pool grows dynamically as required by Oracle Streams.
The (initial) size also depends on usage of ASMM, AMM or manual (minimum) settings.
That means that the parameter STREAMS_POOL_SIZE=0 is not the real root cause but the memory management cannot provide the automatic increase for the DataPump action at this time.
Setting STREAMS_POOL_SIZE>0 will guarantee a minimum size for the streams pool when using ASMM or AMM, hence avoiding the ORA-4031.
Solution
Set the STREAMS_POOL_SIZE instance parameter to at least 48MB to guarantuee a minimum size using:
SQL>connect / as sysdba
SQL> show parameter stream
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
streams_pool_size big integer 0
SQL>alter system set streams_pool_size=48m scope=both
Note:
For a large database and/or high workload using streams, the STREAMS_POOL_SIZE parameter may need to be higher (i.e. 150 MB) in order to avoid the ORA-4031 error.
References
NOTE:396940.1 - Troubleshooting and Diagnosing ORA-4031 Error [Video]
在设置streams_pool_size之后再来执行expdp导出正常导出
Export: Release 10.2.0.4.0 - 64bit Production on Monday, 17 February, 2014 11:01:52
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump_RLZY dumpfile=ybcwfull_140217_0946.dmp logfile=ybcwfull_140217_0946.log