Export/Import problem

[R.A. Joemman]

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_C00387 5""


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

[Frank van Bortel]

Re: Export/Import problem

R.A. Joemman wrote:[color=blue]
> 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_C00387 5""
>
>
> 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[/color]

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

[Andras Kovacs]

Re: Export/Import problem

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.

[R.A. Joemman]

Re: Export/Import problem

andkovacs@yahoo .com (Andras Kovacs) wrote in message news:<412ebb69. 0401141214.7353 13c4@posting.go ogle.com>...[color=blue]
> 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.[/color]

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

[sybrandb@yahoo.com]

Re: Export/Import problem

raoul.joemman@w anadoo.nl (R.A. Joemman) wrote in message news:<e4e5e80f. 0401150315.3bbe e4c5@posting.go ogle.com>...[color=blue]
> andkovacs@yahoo .com (Andras Kovacs) wrote in message news:<412ebb69. 0401141214.7353 13c4@posting.go ogle.com>...[color=green]
> > 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.[/color]
>
> 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[/color]


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

[Andras Kovacs]

Re: Export/Import problem

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.

[R.A. Joemman]

Re: Export/Import problem

andkovacs@yahoo .com (Andras Kovacs) wrote in message news:<412ebb69. 0401141214.7353 13c4@posting.go ogle.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

[sybrandb@yahoo.com]

Re: Export/Import problem

raoul.joemman@w anadoo.nl (R.A. Joemman) wrote in message news:<e4e5e80f. 0401150315.3bbe e4c5@posting.go ogle.com>...

andkovacs@yahoo .com (Andras Kovacs) wrote in message news:<412ebb69. 0401141214.7353 13c4@posting.go ogle.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