Shulou(Shulou.com)05/31 Report--

This article will explain in detail what to do when you encounter IMP-00017 ORA-00942 in the import of IMP. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

IMP-00017,IMP-00003 error encountered during import

[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.

The object exists in the database, although the terminated successfully is still not imported successfully, in fact, it is not only the failure of the ddl table statement shown in the log, but the failure of the whole import. The most important thing to focus on is not successfully but the number of imported rows rows and warnings.

[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 (*)

-

fourteen

The import was successful, but there were warnings. Take out the statements in the log and execute them separately.

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

two

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

That is, the common problem of foreign key failure. Because the table associated with the foreign key is not imported at the time of import, the foreign key is invalid and cannot be created normally.

On the import of IMP IMP-00017 ORA-00942 how to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.