最近使用数据泵（datapump)比较多，遇到了奇奇怪怪的问题，似乎apply了补丁psu 10.2.0.5.180717后，datapump的问题就格外多。如下所示：
expdp system/xxx directory=dumpdir dumpfile=xxxx.dmp tables=xxxx.xxxx logfile=expdp.log
export: release 10.2.0.5.0 - 64bit production on monday, 19 august, 2019 9:52:07
copyright (c) 2003, 2007, oracle. all rights reserved.
connected to: oracle database 10g release 10.2.0.5.0 - 64bit production
ora-31626: job does not exist
ora-31637: cannot create job sys_export_table_01 for user system
ora-06512: at "sys.dbms_sys_error", line 95
ora-06512: at "sys.kupv$ft_int", line 672
ora-39080: failed to create queues "" and "" for data pump job
ora-06512: at "sys.dbms_sys_error", line 95
ora-06512: at "sys.kupc$que_int", line 1606
ora-01403: no data found
遇到这个错误，最开始我以为是我之前遇到的是同一个问题“expdp 导数错误 ora-00832”，检查发现__streams_pool_size大小不为0
sql> col name for a36;
sql> col value for a10;
sql> col idfefault for a10;
col ismod for a10;
col isadj for a10;
sql> select x.ksppinm name ,
y.ksppstvl value ,
y.ksppstdf idfefault ,
decode(bitand(y.ksppstvf,7), 1, 'modified', 4, 'system_mod', 'false') ismod,
decode(bitand(y.ksppstvf,2), 2, 'true', 'false') isadj
from sys.x$ksppi x,
sys.x$ksppcv y
where x.inst_id = userenv('instance') and
y.inst_id = userenv('instance') and
x.indx = y.indx and
x.ksppinm like '%_streams%'
order by translate(x.ksppinm, '_', '');
name value idfefault ismod isadj
------------------------------------ ---------- ---------- ---------- ----------
__streams_pool_size 33554432 false false false
_memory_broker_shrink_streams_pool 900 true false false
_disable_streams_pool_auto_tuning false true false false
_streams_pool_max_size 0 true false false
然后开启跟踪‘1403 trace name errorstack level 3’，执行导出命名后，然后关闭跟踪‘1403 trace name errorstack off’
sql> alter system set events ‘1403 trace name errorstack level 3’;
system altered.
run the expdp command
sql> alter system set events ‘1403 trace name errorstack off’;
system altered.
查看trace文件，如下截图所示，提示“ksedmp:internal or fatal error” ,搜索了一下metalink，发现还真有一模一样的错误
但是这个案例中，在验证表结构时，发现表不存在，所以必须reload the datapump utility， reload the datapump utility候就能正常的导入导出了。
sql> analyze table kupc$datapump_quetab validate structure;
analyze table kupc$datapump_quetab validate structure
*
error at line 1:
ora-00942: table or view does not exist
具体的官方文档如下所示：
datapump import or export (impdp/expdp) fails with errors ora-31626 ora-31637 (文档 id 345198.1)
applies to:
oracle database – enterprise edition – version 10.1.0.4 and later
oracle database cloud schema service – version n/a and later
oracle database exadata cloud machine – version n/a and later
oracle cloud infrastructure – database service – version n/a and later
oracle database backup service – version n/a and later
information in this document applies to any platform.
symptoms
trying to execute the datapump export or import ends with errors like:
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 601
ora-39080: failed to create queues “” and “” for data pump job
ora-06512: at “sys.dbms_sys_error”, line 95
ora-06512: at “sys.kupc$que_int”, line 1550
ora-01403: no data found
you can also see errors like:
ora-39006 – internal error
ora-39065: unexpected master process exception in dispatch
ora-01403: no data found
ora-39097: data pump job encountered unexpected error 100
changes
cause
such errors typically happen in instances with incorrectly configured advanced queueing (aq).
solution
to resolve the issue, follow the steps below:
1. set the error stack 1403 event to know exactly which sql is failing:
connect / as sysdba
alter system set events ‘1403 trace name errorstack level 3’;
note:
in databases that are heavy loaded, setting this event will produce many trace files. it is recommended to set this when the database is not intensively used.
then re-run the datapump export/import to reproduce the error and then disable the events.
alter system set events ‘1403 trace name errorstack off’;
2. once the trace file reveals the problematic sql, check for the existence of table kupc$datapump_quetab using dba_objects.
3. if present, then run:
connect / as sysdba
analyze table kupc$datapump_quetab validate structure;
4. if not present or it errors out in step 3, then reload the datapump utility as described in note 430221.1.