问题：

执行EXPDP导出时报错

Export: Release 10.2.0.5.0 - 64bit Production on Tuesday, 17 November, 2015 10:25:57

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user INSIGHT
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 389
ORA-39077: unable to subscribe agent KUPC$A_1_20151117102602 to queue "KUPC$C_1_20151117102559"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 249
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

原因：

是由于datapump组件中sequence数值大于6位数或sequence失效导致。

SELECT SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N.CURRVAL val# FROM DUAL;

解决办法：

根据MOS上查找，发现（文档 ID 1550344.1和文档 ID 1327004.1）与问题相同。

根据MOS的解决方法：

第一步.先重建data pump

connect / as sysdba

spool output.txt
show user
-- re-install DataPump types and views
@$ORACLE_HOME\rdbms\admin\catdph.sql
-- re-install tde_library packages
@$ORACLE_HOME\rdbms\admin\prvtdtde.plb
-- re-install DataPump packages
@$ORACLE_HOME\rdbms\admin\catdpb.sql
-- re-install DBMS DataPump objects
@$ORACLE_HOME\rdbms\admin\dbmspump.sql
-- recompile invalid objects
@$ORACLE_HOME\rdbms\admin\utlrp.sql

spool off

第二步.重建目录

connect / as sysdba
spool source.out
set lines 120
col status for a9
col object_type for a20;
col owner.object for a50
show user
show parameter service
select instance_name from v$instance;
-- re-create directory object:
create or replace directory EXP_DMP as '';
grant read, write on directory EXP_DMP to system;
-- check status
select status, object_id, object_type,
       owner||'.'||object_name "OWNER.OBJECT"
from   dba_objects
where status != 'VALID' and object_name not like 'BIN$%' order by 4,2;
select status, object_id, object_type,
       owner||'.'||object_name "OWNER.OBJECT"
from   dba_objects
where object_name = 'MY_DIR';

spool off;

过程:

根据MOS上查找，发现（文档 ID 1550344.1和文档 ID 1327004.1）与问题相同。

是由于datapump组件中sequence数值大于6位数或sequence失效导致。

查询NMCDB与BOMCDB的序列值均大于6位

NMCDB：

SQL> SELECT SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N.CURRVAL val# FROM DUAL;
VAL#
----------
1829683

BOMCDB：

SQL> SELECT SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N.CURRVAL val# FROM DUAL;
VAL#
----------
1011636

使用MOS提供的方法解决。

无需停机，无风险

ITPub博客

EXPDP导出报错ORA-39077 ORA-06512

最新文章