ITPub博客

首页 > Linux操作系统 > Linux操作系统 > expdp遭遇ORA-39166?

expdp遭遇ORA-39166?

原创 Linux操作系统 作者:NinGoo 时间:2019-05-17 12:21:05 1 删除 编辑

在使用expdp导出table时,一直报ORA-39166找不到对象?而table明明是存在的,甚至在sqlplus里面使用host+同样的expdp就能成功导出?

http://www.itpub.net/735886.html


E:>expdp ning/ning directory=tmp dumpfile=test.dmp tables=ning.t;

Export: Release 10.2.0.3.0 - Production on Wednesday, 28 March, 2007 10:03:37

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "NING"."SYS_EXPORT_TABLE_01": ning/******** directory=tmp dumpfile=test.dmp tables=ning.t;
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39165: Schema NING was not found.
ORA-39166: Object T; was not found.
ORA-31655: no data or metadata objects selected for job
Job "NING"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at 10:03:45

E:>sqlplus ning/ning

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Mar 28 10:04:16 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

host">NING@ning>host expdp ning/ning directory=tmp dumpfile=test.dmp tables=ning.t;

Export: Release 10.2.0.3.0 - Production on Wednesday, 28 March, 2007 10:04:26

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "NING"."SYS_EXPORT_TABLE_01": ning/******** directory=tmp dumpfile=test.dmp tables=ning.t
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "NING"."T" 9.421 KB 10 rows
Master table "NING"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
***************************************************************
Dump file set for NING.SYS_EXPORT_TABLE_01 is:
D:TEST.DMP
Job "NING"."SYS_EXPORT_TABLE_01" successfully completed at 10:04:42

出现该状况,请仔细检查你的expdp命令,是不是在最后加了个逗号?犯了像上面链接里的楼主一样错误?习惯在sqlplus将每个命令加上逗号后,这个错误还是比较容易犯的,我也是在遇到这个状况后google到上面的帖子的。

出现该问题的原因在于,在os的命令行上使用了分号做为结尾,实际上是不需要的,不但是多次一举,还导致expdp将后面的分号也算到表名里面去了,即使你将表名加上括号加上引号也没用,expdp会将分号也当成一个对象名来导出,自然无法找到对象,这个应该也算是oracle的一个bug。

而在sqlplus里,由于命令默认是分号结束的,所以没有问题。

实际上,oracle的expdp确实也有两个bug是会导致出现ORA-39166错误的,一个是table的名字里含有小写字母,也就是建表的时候将表名用双引号引起来,里面是有小写字母的。Bug No. 5606526

还有一种情况是导出sys schema下的对象的时候 Bug No. 5402988

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50321/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle block的类型
请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    79
  • 访问量
    55705