IMP导入遇到IMP

61次阅读

共计 3198 个字符，预计需要花费 8 分钟才能阅读完成。

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

导入时遇到 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 SCOTT s 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 SCOTT s 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
也就是常见的外键失效的问题。因为导入的时候没有导入外键关联的表，所以外键失效，无法正常创建。