By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,196 Members | 973 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,196 IT Pros & Developers. It's quick & easy.

Export/Import problem

P: n/a
Hi,

We are migrating a database from Oracle 8.1.7.0.0 to Oracle 9.2.0.1.0
on AIX.
Export as follows:

exp ${user}/${pwd} compress=n file=${file} log=${log} owner=${schema}
grants=y indexes=y rows=y constraints=y

Then import as follows:

imp ${user}/${pwd} file=${file} log=${log} full=y

On import we get errors because of the constraint names are renamed by
the target dbms:

IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "xxx" ENABLE NOVALIDATE CONSTRAINT "SYS_C003875""
The DBMS has renamed the constraint from SYS_C003875 to SYS_C004600.
So the above statement fails.
How do we tell the DBMS to not rename the constraints?
Best regards
Raoul
Jul 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
R.A. Joemman wrote:
Hi,

We are migrating a database from Oracle 8.1.7.0.0 to Oracle 9.2.0.1.0
on AIX.
Export as follows:

exp ${user}/${pwd} compress=n file=${file} log=${log} owner=${schema}
grants=y indexes=y rows=y constraints=y

Then import as follows:

imp ${user}/${pwd} file=${file} log=${log} full=y

On import we get errors because of the constraint names are renamed by
the target dbms:

IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "xxx" ENABLE NOVALIDATE CONSTRAINT "SYS_C003875""
The DBMS has renamed the constraint from SYS_C003875 to SYS_C004600.
So the above statement fails.
How do we tell the DBMS to not rename the constraints?
Best regards
Raoul


This will teach you not to name your contraints!
No other way that to query afterwards (your unnamed constraints will
be there) and enable them
--

Regards,
Frank van Bortel

Jul 19 '05 #2

P: n/a
This problem is right at the beginning of the export file. if you move
from IBM to Unix you might have a char convertion problem from ebcdc
to ascii right on the OS. Usually when we migrate DBs across OSs we
do the export and the import within the same server. You can load your
data from the IBM box to the Unix box if you set up Net8 in your
tnsname.ora file. Export your staff on the IBM then try to load from
the IBM box the data to the Unix box.

Another thing: I wouldn't use record_length parameter in this case.

Good luck.
Jul 19 '05 #3

P: n/a
an*******@yahoo.com (Andras Kovacs) wrote in message news:<41**************************@posting.google. com>...
This problem is right at the beginning of the export file. if you move
from IBM to Unix you might have a char convertion problem from ebcdc
to ascii right on the OS. Usually when we migrate DBs across OSs we
do the export and the import within the same server. You can load your
data from the IBM box to the Unix box if you set up Net8 in your
tnsname.ora file. Export your staff on the IBM then try to load from
the IBM box the data to the Unix box.

Another thing: I wouldn't use record_length parameter in this case.

Good luck.


Hi Andreas,

Per accident you have answered a old question from 1994 of Fredrik
Nyman.
For some reason Google has implemented my question into his, I think
because of the subject of the question that is the same.
Can you please look at my question #2? Maybe you have a answer on it
for me?

Regards
Raoul
Jul 19 '05 #4

P: n/a
ra***********@wanadoo.nl (R.A. Joemman) wrote in message news:<e4**************************@posting.google. com>...
an*******@yahoo.com (Andras Kovacs) wrote in message news:<41**************************@posting.google. com>...
This problem is right at the beginning of the export file. if you move
from IBM to Unix you might have a char convertion problem from ebcdc
to ascii right on the OS. Usually when we migrate DBs across OSs we
do the export and the import within the same server. You can load your
data from the IBM box to the Unix box if you set up Net8 in your
tnsname.ora file. Export your staff on the IBM then try to load from
the IBM box the data to the Unix box.

Another thing: I wouldn't use record_length parameter in this case.

Good luck.


Hi Andreas,

Per accident you have answered a old question from 1994 of Fredrik
Nyman.
For some reason Google has implemented my question into his, I think
because of the subject of the question that is the same.
Can you please look at my question #2? Maybe you have a answer on it
for me?

Regards
Raoul

The answer is you have all your constraints with system generated
names, and the only way to resolve it is to give your constraint
explicit names prior to export. You can't stop import from renaming
constraint. IIRC the manual contains explicit warnings against not
naming constraints.

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #5

P: n/a
This problem is right at the beginning of the export file. if you move
from IBM to Unix you might have a char convertion problem from ebcdc
to ascii right on the OS. Usually when we migrate DBs across OSs we
do the export and the import within the same server. You can load your
data from the IBM box to the Unix box if you set up Net8 in your
tnsname.ora file. Export your staff on the IBM then try to load from
the IBM box the data to the Unix box.

Another thing: I wouldn't use record_length parameter in this case.

Good luck.
Jun 27 '08 #6

P: n/a
an*******@yahoo.com (Andras Kovacs) wrote in message news:<41**************************@posting.google. com>...
This problem is right at the beginning of the export file. if you move
from IBM to Unix you might have a char convertion problem from ebcdc
to ascii right on the OS. Usually when we migrate DBs across OSs we
do the export and the import within the same server. You can load your
data from the IBM box to the Unix box if you set up Net8 in your
tnsname.ora file. Export your staff on the IBM then try to load from
the IBM box the data to the Unix box.

Another thing: I wouldn't use record_length parameter in this case.

Good luck.
Hi Andreas,

Per accident you have answered a old question from 1994 of Fredrik
Nyman.
For some reason Google has implemented my question into his, I think
because of the subject of the question that is the same.
Can you please look at my question #2? Maybe you have a answer on it
for me?

Regards
Raoul
Jun 27 '08 #7

P: n/a
ra***********@wanadoo.nl (R.A. Joemman) wrote in message news:<e4**************************@posting.google. com>...
an*******@yahoo.com (Andras Kovacs) wrote in message news:<41**************************@posting.google. com>...
This problem is right at the beginning of the export file. if you move
from IBM to Unix you might have a char convertion problem from ebcdc
to ascii right on the OS. Usually when we migrate DBs across OSs we
do the export and the import within the same server. You can load your
data from the IBM box to the Unix box if you set up Net8 in your
tnsname.ora file. Export your staff on the IBM then try to load from
the IBM box the data to the Unix box.

Another thing: I wouldn't use record_length parameter in this case.

Good luck.

Hi Andreas,

Per accident you have answered a old question from 1994 of Fredrik
Nyman.
For some reason Google has implemented my question into his, I think
because of the subject of the question that is the same.
Can you please look at my question #2? Maybe you have a answer on it
for me?

Regards
Raoul

The answer is you have all your constraints with system generated
names, and the only way to resolve it is to give your constraint
explicit names prior to export. You can't stop import from renaming
constraint. IIRC the manual contains explicit warnings against not
naming constraints.

Sybrand Bakker
Senior Oracle DBA
Jun 27 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.