Home » RDBMS Server » Server Utilities » Oracle errors on import (All sorts of errors on import)
icon5.gif  Oracle errors on import [message #275207] Thu, 18 October 2007 15:51 Go to next message
cattanac
Messages: 2
Registered: October 2007
Location: NZ
Junior Member
Hi there,

First try at using this site. Hope you can help.

I am supporting a client and have a db backup done using exp and a parfile.

Now I usually just use the exp and imp commands to transfer databases. I am having HUGE problems with this particular client as they pass me a dmp file and a parfile but I can never import it without errors.

I run imp, login as a user I have created and then using the imp defaults, attempt to import the file contents, (user only).

Errors in the main:
. . importing table "PMQ_DBLINK_MAPPING" 14 rows imported
IMP-00017: following statement failed with ORACLE error 921:
"CREATE UNIQUE INDEX "PK_PMQ_DBLINK_MAPPING" ON "PMQ_DBLINK_MAPPING" ("
IMP-00003: ORACLE error 921 encountered
ORA-00921: unexpected end of SQL command

So it imports the table then fails on index creation?

When it comes to do the constraints:
IMP-00017: following statement failed with ORACLE error 2270:
"ALTER TABLE "TR_ACTIVITYACTIONMAP" ADD CONSTRAINT "FK_TR_AAM_AVTID" FOREIGN"
" KEY ("ACTIVITYID") REFERENCES "TR_ACTIVITY" ("ACTIVITYID") ENABLE NOVALIDA"
"TE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list

Help?
Re: Oracle errors on import [message #275249 is a reply to message #275207] Fri, 19 October 2007 00:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is likely that you dmp file is corrupted.
Did you tranfer it between machines in NON binary mode?

Regards
Michel
Re: Oracle errors on import [message #275251 is a reply to message #275207] Fri, 19 October 2007 00:14 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Such bizarre errors can result from using the "imp" utility version which does not correspond the same version which made the dump file.
Re: Oracle errors on import [message #275510 is a reply to message #275207] Sat, 20 October 2007 22:57 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I think it is not a problem of that. It is likely that your exported database was not consistent while you took the dump.
Re: Oracle errors on import [message #275511 is a reply to message #275207] Sat, 20 October 2007 23:00 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What I mean is that ,
arju@TERMINUS>create table dept (dept_id number );

Table created.

arju@TERMINUS>create table std (id number);

Table created.
arju@TERMINUS>alter table std add primary key (id);

Table altered.

arju@TERMINUS>alter table dept add foreign key(dept_id) references std(id);

Table altered.

arju@TERMINUS>host exp arju/arju tables=dept file=/backup/check.dmp

Export: Release 10.2.0.2.0 - Production on Sat Oct 20 23:50:02 2007

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           DEPT          0 rows exported
Export terminated successfully without warnings.
arju@TERMINUS>drop table dept;

Table dropped.

arju@TERMINUS>drop table std;

Table dropped.

arju@TERMINUS>create table std( id number);

Table created.

arju@TERMINUS>host imp arju/arju file=/backup/check.dmp full=y

Import: Release 10.2.0.2.0 - Production on Sat Oct 20 23:53:12 2007

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


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

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing ARJU's objects into ARJU
. importing ARJU's objects into ARJU
. . importing table                         "DEPT"          0 rows imported
IMP-00017: following statement failed with ORACLE error 2270:
 "ALTER TABLE "DEPT" ADD FOREIGN KEY ("DEPT_ID") REFERENCES "STD" ("ID") ENAB"
 "LE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
Import terminated successfully with warnings.

Re: Oracle errors on import [message #275525 is a reply to message #275511] Sun, 21 October 2007 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju, the first error is:
ORA-00921: unexpected end of SQL command

You can then ignore the other errors, of course there is no PK as it previously couldn't create it.

Regards
Michel
Re: Oracle errors on import [message #275586 is a reply to message #275525] Sun, 21 October 2007 22:56 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Michel Cadot wrote on Sun, 21 October 2007 12:14

Arju, the first error is:
ORA-00921: unexpected end of SQL command

You can then ignore the other errors.

Regards
Michel



Oracle sometimes give fake message while actually hide the real one. Can you please have a look on metalink address of ,
https://metalink.oracle.com/metalink/plsql/f?p=130:14:2536506163093105830::::p14_database_id,p14_docid,p14_show_header,p14_show_help, p14_black_frame,p14_font:NOT,228516.1,1,1,1,helvetica

section problems

Thank you.
Re: Oracle errors on import [message #275621 is a reply to message #275586] Mon, 22 October 2007 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju,

This doc is quite long, please precise the point you are refering.
Anyway, exception is not the rule, we have to verify the most likely before saying we are in an exception case and ignore the first message.

Regards
Michel
Re: Oracle errors on import [message #276390 is a reply to message #275207] Thu, 25 October 2007 01:29 Go to previous message
cattanac
Messages: 2
Registered: October 2007
Location: NZ
Junior Member
Thanks all,

I am going to sit with the DBA for the client next week and run through what they are doing and how they restore it. Should be fun.

CC
Previous Topic: import whole database
Next Topic: Sequence Issues After Export / Import
Goto Forum:
  


Current Time: Sat Dec 03 04:15:33 CST 2016

Total time taken to generate the page: 0.18510 seconds