天萃荷净
oracle expdp遭遇ORA-39006/ORA-39213故障解决办法
expdp导出数据遇到ORA-39006/ORA-39213错误,通过执行dbms_metadata_util.load_stylesheets解决
expdp工作异常
--导出awr信息
SQL> @?/rdbms/admin/awrextr.sql
…………
Exception encountered in AWR_EXTRACT
ORA-39006: internal error
ORA-39213: Metadata processing is not available
begin
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4710
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 656
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 962
ORA-06512: at line 3
--导出一个表
$ expdp "'/ as sysdba'" dumpfile=xifenfei.dmp tables=scott.t_xifenfei
Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 31 October, 2012 13:03:20
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39006: internal error
ORA-39213: Metadata processing is not available
查看oracle错误提示
$ oerr ora 39006
39006, 00000, "internal error"
// *Cause: An unexpected error occurred while processing a Data Pump job.
// Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS
// will further describe the error.
// *Action: Contact Oracle Customer Support.
$ oerr ora 39213
39213, 00000, "Metadata processing is not available"
// *Cause: The Data Pump could not use the Metadata API. Typically,
// this is caused by the XSL stylesheets not being set up properly.
// *Action: Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets
// to reload the stylesheets.
解决ORA-39006/ORA-39213故障思路
--查询数据库已经安装组件
SQL> col COMP_NAME for a35
SQL> select comp_name, version, status from dba_registry;
COMP_NAME VERSION STATUS
----------------------------------- ------------------------------ ----------------------
Oracle Database Catalog Views 10.2.0.1.0 VALID
Oracle Database Packages and Types 10.2.0.1.0 VALID
Oracle Workspace Manager 10.2.0.1.0 VALID
JServer JAVA Virtual Machine 10.2.0.1.0 VALID
Oracle XDK 10.2.0.1.0 VALID
Oracle Database Java Packages 10.2.0.1.0 VALID
Oracle Expression Filter 10.2.0.1.0 VALID
Oracle Data Mining 10.2.0.1.0 VALID
Oracle Text 10.2.0.1.0 VALID
Oracle XML Database 10.2.0.1.0 VALID
Oracle Rules Manager 10.2.0.1.0 VALID
Oracle interMedia 10.2.0.1.0 VALID
OLAP Analytic Workspace 10.2.0.1.0 VALID
Oracle OLAP API 10.2.0.1.0 VALID
OLAP Catalog 10.2.0.1.0 VALID
Spatial 10.2.0.1.0 VALID
Oracle Enterprise Manager 10.2.0.1.0 VALID
17 rows selected.
--如果缺少下面组件,使用下面对应的程序安装
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
--使用下面脚本安装(根据组件选择)
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/xdk/admin/initxml.sql
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/catjava.sql
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
--执行sys.dbms_metadata_util.load_stylesheets
SQL> execute sys.dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.
测试expdp导出案例
$ expdp "'/ as sysdba'" dumpfile=xifenfei.dmp tables=scott.t_xifenfei Directory=AWR_DIR
Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 31 October, 2012 14:18:04
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01": '/******** AS SYSDBA' dumpfile=xifenfei.dmp
tables=scott.t_xifenfei Directory=AWR_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 7 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T_XIFENFEI" 5.374 MB 57376 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/data/enmotech/xifenfei.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:18:11
测试证明,在不缺少相关组件的情况下,使用dbms_metadata_util.load_stylesheets可以解决expdp导出报ORA-39006/ORA-39213错误;如果缺少组件,需要先安装对应组件,然后再执行dbms_metadata_util.load_stylesheets解决该问题
-----------------温馨提示--------------------
操作有风险,动手需谨慎
Oracle研究中心
http://www.oracleplus.net
本文由大师惜分飞原创分享,转载请尽量保留本站网址
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之案例:expdp数据泵导出时ORA-39006 internal error ORA-39213 Met