当前位置:  数据库>oracle

Oracle 10g expdp导出报错ORA-4031的解决方法

    来源: 互联网  发布时间：2017-06-03

    本文导语: 数据库是10.2.0.4 操作系统是aix,在执行expdp导出多个方案对象时报ORA-39014,ORA-39029,ORA-31671,ORA-39079,ORA-06512,ORA-04031:错误信息如下: Export: Release 10.2.0.4.0 - 64bit Production on Monday, 17 February, 2014 9:46:52 Copyright (c) 2003, 2007, Oracle. All rights reserv...

数据库是10.2.0.4 操作系统是aix,在执行expdp导出多个方案对象时报ORA-39014,ORA-39029,ORA-31671,ORA-39079,ORA-06512,ORA-04031:错误信息如下:
 Export: Release 10.2.0.4.0 - 64bit Production on Monday, 17 February, 2014 9:46: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 schemas=ZW
 1001,ZW1002,ZW1003,ZW1004,ZW1005,ZW1006,ZW1101,ZW1102,ZW1103,ZW1104,ZW1105,ZW1106,ZW1201,ZW1202,ZW1203,ZW1204,ZW1205,ZW1206,ZW1301,ZW1302,ZW1303,ZW1304,ZW13
 05,ZW1306,ZW2001,ZW2002,ZW2003,ZW2004,ZW2005,ZW2006,ZW3001,ZW3002,ZW3003,ZW3004,ZW3005,ZW3006,ZW4001,ZW4002,ZW4003,ZW4004,ZW4005,ZW4006,ZW5001,ZW5002,ZW5003
 ,ZW5004,ZW5005,ZW5006,ZW6001,ZW6002,ZW6003,ZW6004,ZW6005,ZW6006,ZW7001,ZW7002,ZW7003,ZW7004,ZW7005,ZW7006,ZW8001,ZW8002,ZW8003,ZW8004,ZW8005,ZW8006,ZW9001,Z
 W9002,ZW9003,ZW9004,ZW9005,ZW9006,ZW9999
 Estimate in progress using BLOCKS method...
 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 997.3 MB
 Processing object type SCHEMA_EXPORT/USER
 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
 Processing object type SCHEMA_EXPORT/ROLE_GRANT
 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
 Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
 Processing object type SCHEMA_EXPORT/TABLE/TABLE
 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
 Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
 Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
 Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
 Processing object type SCHEMA_EXPORT/VIEW/VIEW
 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
 Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 ..................
 . . exported "ZW9005"."GLSP_KMYS_TEMP" 0 KB 0 rows
 . . exported "ZW9005"."GLSP_KMZJS_TEMP" 0 KB 0 rows
 . . exported "ZW9005"."GLSP_KMZXFX_TEMP" 0 KB 0 rows
 . . exported "ZW9005"."GLSP_PZYJZ_TEMP1" 0 KB 0 rows
 . . exported "ZW9005"."GLSP_PZYJZ_TEMP2" 0 KB 0 rows
 . . exported "ZW9005"."GLSP_PZYJZ_TEMP3" 0 KB 0 rows
 . . exported "ZW9005"."GLSP_PZYJZ_TEMP4" 0 KB 0 rows
 . . exported "ZW9005"."GLSP_PZYJZ_TEMP5" 0 KB 0 rows
 . . exported "ZW9005"."GLSP_PZYJZ_TEMP6" 0 KB 0 rows
 ORA-39014: One or more workers have prematurely exited.
 ORA-39029: worker 1 with process name "DW01" prematurely terminated
 ORA-31671: Worker process DW01 had an unhandled exception.
 ORA-39079: unable to enqueue message DG,KUPC$C_1_20140217094653,KUPC$A_1_20140217094654,MCP,50443,Y
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
 ORA-06512: at "SYS.KUPC$QUE_INT", line 924
 ORA-04031: unable to allocate 2072 bytes of shared memory ("streams pool","unknown object","streams pool","kodpaih3 image")
 ORA-06512: at "SYS.KUPW$WORKER", line 1397
 ORA-06512: at line 2

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 schemas=ZW
 1001,ZW1002,ZW1003,ZW1004,ZW1005,ZW1006,ZW1101,ZW1102,ZW1103,ZW1104,ZW1105,ZW1106,ZW1201,ZW1202,ZW1203,ZW1204,ZW1205,ZW1206,ZW1301,ZW1302,ZW1303,ZW1304,ZW13
 05,ZW1306,ZW2001,ZW2002,ZW2003,ZW2004,ZW2005,ZW2006,ZW3001,ZW3002,ZW3003,ZW3004,ZW3005,ZW3006,ZW4001,ZW4002,ZW4003,ZW4004,ZW4005,ZW4006,ZW5001,ZW5002,ZW5003
 ,ZW5004,ZW5005,ZW5006,ZW6001,ZW6002,ZW6003,ZW6004,ZW6005,ZW6006,ZW7001,ZW7002,ZW7003,ZW7004,ZW7005,ZW7006,ZW8001,ZW8002,ZW8003,ZW8004,ZW8005,ZW8006,ZW9001,Z
 W9002,ZW9003,ZW9004,ZW9005,ZW9006,ZW9999
 Estimate in progress using BLOCKS method...
 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 997.3 MB
 Processing object type SCHEMA_EXPORT/USER
 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
 Processing object type SCHEMA_EXPORT/ROLE_GRANT
 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
 Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
 Processing object type SCHEMA_EXPORT/TABLE/TABLE
 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
 Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
 Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
 Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
 Processing object type SCHEMA_EXPORT/VIEW/VIEW
 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
 Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 ...................
 . . exported "ZW9999"."ZB_SKR" 0 KB 0 rows
 . . exported "ZW9999"."ZB_TKPZML" 0 KB 0 rows
 . . exported "ZW9999"."ZB_TKPZNR" 0 KB 0 rows
 . . exported "ZW9999"."ZB_WHSZ" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YEYKJH" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YEZFPZ" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YHBM" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YHZL" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YJZPML" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YJZPNR" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YKJH" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YKJHDR" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YKJHHQB" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YKJHML" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YKJHNR" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YSDWGX" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YSDZGS" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YSGLLX" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YSKMGX" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YSKMKJDY" 0 KB 0 rows
 . . exported "ZW9999"."ZB_YSKMKJGX" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZBBD" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZBHQB" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZBLY" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZCLX" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFBKD" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFFS" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFPZFJ" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFPZML" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFPZML_Y" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFPZNR" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFPZNR_Y" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFSQDFJ" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFSQDML" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFSQDNR" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZFSQHQB" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZJBFLX" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZLZLX" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZLZML" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZYXX" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZZB" 0 KB 0 rows
 . . exported "ZW9999"."ZB_ZZB_S" 0 KB 0 rows
 Master table "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:
 /yb_oradata/RLZYbak/dpdump/ybcwfull_140217_1102.dmp
 Job "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully completed at 11:41:05


    
 
 

您可能感兴趣的文章:

  • oracle数据库导出和oracle导入数据的二种方法(oracle导入导出数据)
  • 11g Oracle导出表不导出数据默认为空表的解决方法
  • Oracle exp 导出用户对象
  • oracle 数据泵导入导出介绍
  • 利用多个转储文件导出大量Oracle数据
  • linux环境下oracle条件导出数据的shell脚本怎么写
  • Linux环境中Oracle数据导入与导出备份操作
  • 用Oracle管理服务器将数据导入与导出
  • oracle的导入导出注意事项及心得分享
  • Oracle10201 RAC升级到10204后导出数据时报EXP-00056错误
  • Oracle的导入导出的问题
  • oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
  • Linux系统下导出ORACLE数据库出现Exporting questionable statistics.错误 处理
  • oracle 11g导出数据时报ORA 1455错误的处理方法
  • oracle数据库导入导出命令使用方法
  • 手把手教你Oracle数据库导出数据库结构到PowerDesigner
  • Oracle统计信息的导出与导入
  • 浅析Oracle对象和数据的导入导出
  • Oracle数据库密码重置、导入导出库命令示例应用
  • ORACLE EXP不能导出空表的原因分析及解决方法
  • 解决oracle用户连接失败的解决方法
  • Oracle Connect to Idle Instance解决方法
  • Linux下安装Tomcat后与Oracle冲突的解决
  • linux下安装oracle,出现没有权限的报警,怎么解决?
  • 安装Oracle加载数据库错误areasQueries的解决
  • Oracle 11g 相关工具netca,dbca乱码之解决
  • Linux安装Oracle 11时报错DISPLAY解决方案
  • Linux下Oracle安装时遇到的问题及解决
  • 简单的Oracle小问题解决
  • oracle中文乱码解决的办法
  • 关于系统重装后Oracle数据库完全恢复的解决办法
    •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐
  • linux下安装oracle后使用命令行启动的方法 linux启动oracle
  • ORACLE 中修改用户密码的方法
  • Linux下完全卸载ORACLE 10G的方法
  • 将Oracle 8i数据成功移植Oracle 10g的方法
  • oracle增加表空间大小两种实现方法
  • linux下用ODBC链接Oracle怎么连啊？跪求具体方法！！！
  • Linux系统下查看oracle SID的方法
  • oracle 彻底删除方法
  • window中oracle环境变量设置方法分享
  • Oracle 忘记密码的找回方法
  • Oracle中serveroutput参数一次设置永久保存方法
  • Oracle指定IP访问数据库方法
  • Oracle SID存在解決方法
  • 登录oracle数据库时密码忘记的解决方法
  • PB7 连接 Oracle 的设置方法
  • Oracle删除后不能重新安装的解决方法
  • oracle删除文件后数据库启动不了的处理方法
  • 跪求UNIX 下C用OCI连接ORACLE方法。高手进！！！
  • 彻底删除Oracle数据库的方法
  • oracle drop table(表)数据恢复方法
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动！也就是让oracle那个服务自动启动，不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同？
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,