EXPDP/IMPDP 导入导出报错http://www.cndba.cn/Marvinn/article/2670

接到客户反馈，数据泵impdp导入报错，以下是关于ora-31623数据泵错误处理http://www.cndba.cn/Marvinn/article/2670

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

查看ORA-31623报错解释：

Question:  I am running a Data Pump import (impdp) and I get the following ORA-31623 error:

ORA-31623: a job is not attached to this session via the specified handle

 How do I fix the ORA-31623 error: a job is not attached to this session via the specified handle?

Answer:  Data Pump import and export job use queues, streams and java objects.

 If we see any job component is invalid (e.g. dbms_java, queue dictionary scripts not run, rdbms/admin/utlrp.sql not run) we may see the ORA-31623  error.

解决问题思路：http://www.cndba.cn/Marvinn/article/2670http://www.cndba.cn/Marvinn/article/2670

1、查看导出导出用户是否具备权限
2、查看是否有相关组件失效

connect / as sysdba

SELECT comp_name, status, version

FROM dba_registry;

Oracle olap api状态跟数据泵无关，可忽略，http://www.cndba.cn/Marvinn/article/2670

Oracle Database Packages and types 状态为loading 加载，这个状态表名该组件一直在加载没加载出，可理解为无效，但是数据泵需要调用DBMS包，需哟啊用到该组件，怀疑是这个组件问题

直接编译后查询

@？rdbms/admin/utlrp.sql
connect / as sysdba

SELECT comp_name, status, version

FROM   dba_registry where status = 'INVALID';

http://www.cndba.cn/Marvinn/article/2670

其实未编译成功,再次尝试导入，报错已经改变
http://www.cndba.cn/Marvinn/article/2670

再次确认跟 之前设想的组件相关，然后开始针对这个组件进行处理，处理期间需要关库

处理完之后，查询组件正常，显示valid状态，再次尝试导入数据，导入成功，没有报错

版权声明：本文为博主原创文章，未经博主允许不得转载。