IMP导入遇到IMP-00017 ORA-00942怎么办

这篇文章将为大家详细讲解有关IMP导入遇到IMP-00017 ORA-00942怎么办，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

导入时遇到IMP-00017，IMP-00003错误

[oracle@lzl ~]$ cat parfile_imp.txt 

userid="scott/tiger"

file=/home/oracle/exp_lzl.dmp

log=/home/oracle/imp_lzl.log

buffer=102400

tables=emp

fromuser=scott

touser=smith

rows=y

[oracle@lzl ~]$ imp parfile=parfile_imp.txt 

Import: Release 11.2.0.3.0 - Production on Tue Jul 11 01:36:33 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SYS, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SCOTTs objects into SMITH

IMP-00015: following statement failed because the object already exists:

"CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH"

"AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM"

 "BER(7, 2), "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"

"S 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST "

"GROUPS 1 BUFFER_POOL DEFAULT)                        LOGGING NOCOMPRESS"

Import terminated successfully with warnings.

对象存在于数据库中，虽然 terminated successfully但是仍然没有导入成功，实际上并不是日志中显示的只有ddl建表语句执行失败，而是整个导入都失败了，最应该关注的不是successfully而是导入的行数rows以及警告。

[oracle@lzl ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 11 01:44:29 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table smith.emp;

Table dropped.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@lzl ~]$ imp parfile=parfile_imp.txt 

Import: Release 11.2.0.3.0 - Production on Tue Jul 11 01:44:56 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SYS, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SCOTTs objects into SMITH

. . importing table                          "EMP"

.......

                                                           14 rows imported

IMP-00017: following statement failed with ORACLE error 942:

"ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"

 "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"

IMP-00003: ORACLE error 942 encountered

ORA-00942: table or view does not exist

About to enable constraints...

IMP-00017: following statement failed with ORACLE error 2430:

 "ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""

Import terminated successfully with warnings.

SQL> select count(*) from smith.emp;

  COUNT(*)

----------

14

导入已经成功了，但是有警告。把日志中的语句拿出来单独执行

SQL> ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE

  2  

SQL> /

ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE

*

ERROR at line 1:

ORA-00942: table or view does not exist

也就是常见的外键失效的问题。因为导入的时候没有导入外键关联的表，所以外键失效，无法正常创建。

关于IMP导入遇到IMP-00017 ORA-00942怎么办就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。