如果数据库服务器的shared pool过小或碎片过多会导致EXPDP工具无法完成备份，通常伴随如下错误信息： ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE:"SEC"."T3"] ORA-04031: unable to allocate 56 bytes of shared memory ("shared pool","SELECT /*+rule*/ SYS_XMLGEN(...","sql area","idndef*[]: qkexrPackName")
模拟再现一下这个问题，并给出处理方法，供参考。
1.问题再现 1）确认操作系统信息 ora10g@secDB /expdp$ uname -a Linux secDB 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64 GNU/Linux
2）确认数据库版本 sys@ora10g> select * from v$version;
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 9:45:57
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options Starting "SEC"."SYS_EXPORT_SCHEMA_02": sec/******** directory=dump_dir dumpfile=sec.dmp logfile=sec.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 448 KB 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/TABLE/TABLE ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE:"SEC"."T3"] ORA-04031: unable to allocate 56 bytes of shared memory ("shared pool","SELECT /*+rule*/ SYS_XMLGEN(...","sql area","idndef*[]: qkexrPackName")
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.KUPW$WORKER", line 6234
----- PL/SQL Call Stack ----- object line object handle number name 0x664d6bc8 14916 package body SYS.KUPW$WORKER 0x664d6bc8 6293 package body SYS.KUPW$WORKER 0x664d6bc8 2339 package body SYS.KUPW$WORKER 0x664d6bc8 6854 package body SYS.KUPW$WORKER 0x664d6bc8 1259 package body SYS.KUPW$WORKER 0x6661f1a8 2 anonymous block
Job "SEC"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at 09:46:04
2.问题原因 参考MOS的OERR: ORA 4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" [ID 19837.1] Error: ORA 4031 Text: unable to allocate %s bytes of shared memory (%s,%s,%s) ------------------------------------------------------------------------------- Cause: More shared memory is needed than was allocated in the shared pool. Action: Either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the init.ora parameter "shared_pool_size".
