Import and export of database?

Ketil Z Malde

unread,

Jun 25, 1998, 3:00:00 AM6/25/98

to

Hi

Using oracle 7 on Solaris 2.6:

I'm still unable to use the )(&/&/% imp and exp commands to move a
database. Why is this so hard?

Making a full dump to expdat.dmp, ftping the file, and then doing an

imp user/pass file=expdat.dmp full=y ignore=y

doesn't do it, adding a "destroy=y" option doesn't seem to do anything,
and setting inctype to complete etc, gives an error.

The error messages I get talks of constraints being violated, here's a
sample:

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SLADMIN.XPKSL_PPRJ_TREE) violated

Happens for all rows in the dump. Any suggestions, while I still have
some hair left?

~kzm

Joel Garry

unread,

Jun 25, 1998, 3:00:00 AM6/25/98

to

On 25 Jun 1998 16:56:00 +0200, Ketil Z Malde <ke...@ii.uib.no> wrote:
>
>Hi
>
>Using oracle 7 on Solaris 2.6:
>
>I'm still unable to use the )(&/&/% imp and exp commands to move a
>database. Why is this so hard?

Because referential integrity was hacked onto the db, rather than being
there from the beginning.

>
>Making a full dump to expdat.dmp, ftping the file, and then doing an
>
> imp user/pass file=expdat.dmp full=y ignore=y
>
>doesn't do it, adding a "destroy=y" option doesn't seem to do anything,
>and setting inctype to complete etc, gives an error.

>
>The error messages I get talks of constraints being violated, here's a
>sample:
>
>IMP-00019: row rejected due to ORACLE error 1
>IMP-00003: ORACLE error 1 encountered
>ORA-00001: unique constraint (SLADMIN.XPKSL_PPRJ_TREE) violated
>
>Happens for all rows in the dump. Any suggestions, while I still have
>some hair left?

Disable all your constraints before the import. I find the cons_en script
from TUSC pretty useful, just g/enable/s//disable/ and away you go!
The script just builds alter table statements from sys.dba_constraints where
status ENABLED and owner not in SYS or SYSTEM. I'm sure there are a
gazillion similar scripts floating about.

Be careful not to import twice with your constraints disabled, though.

>
>~kzm

--
These opinions are my own and not necessarily those of Information Quest or
Pebble In The Sky http://www.informationquest.com mailto:jga...@nospameiq.com
http://ourworld.compuserve.com/homepages/joel_garry Remove nospam to reply.
mailto:joel_...@compuserve.nospam.com "See your DBA?" I AM the @#%*& DBA!

Ketil Z Malde

unread,

Jun 26, 1998, 3:00:00 AM6/26/98

to

joe...@pebble.org (Joel Garry) writes:

> Disable all your constraints before the import. I find the cons_en script
> from TUSC pretty useful

Hmm anybody have an URL? I assume you mean www.tusc.com, but I've waded
through their advertisement stuff without coming up with anything.

~kzm
--
If I haven't seen further, it is by standing in the footprints of giants

Joel Garry

unread,

Jun 26, 1998, 3:00:00 AM6/26/98

to

On 26 Jun 1998 13:56:18 +0200, Ketil Z Malde <ke...@ii.uib.no> wrote:
>joe...@pebble.org (Joel Garry) writes:
>
>> Disable all your constraints before the import. I find the cons_en script
>> from TUSC pretty useful
>
>Hmm anybody have an URL? I assume you mean www.tusc.com, but I've waded
>through their advertisement stuff without coming up with anything.

Yeah, that's it. Select "documents" next to their obnoxious flashing whatzis.
I think the file is 124.zip. Hmmm, a lot of new stuff there since I last
looked. Zip files are such a pain... for those of us who use real computers.

>
>~kzm
>--
>If I haven't seen further, it is by standing in the footprints of giants

JHY

unread,

Jun 29, 1998, 3:00:00 AM6/29/98

to

Your export should work. When importing use ignore=y. Make sure all the
tablespaces that were in your source database have already been created in
your target database, otherwise import will try to create the tablespace
using the path spec from the source database.

I would drop cascade all non-system/sys users from your target database
before the import. Let the import recreate everything, this way you don't
have to mess around with disabling/enabling constraints.

When you ftp an export file, make sure you set the transfer mode to binary
or image. ASCII mode will corrupt the export file.

HTH

Ketil Z Malde wrote:

> Hi
>
> Using oracle 7 on Solaris 2.6:
>
> I'm still unable to use the )(&/&/% imp and exp commands to move a
> database. Why is this so hard?
>

> Making a full dump to expdat.dmp, ftping the file, and then doing an
>
> imp user/pass file=expdat.dmp full=y ignore=y
>
> doesn't do it, adding a "destroy=y" option doesn't seem to do anything,
> and setting inctype to complete etc, gives an error.
>
> The error messages I get talks of constraints being violated, here's a
> sample:
>
> IMP-00019: row rejected due to ORACLE error 1
> IMP-00003: ORACLE error 1 encountered
> ORA-00001: unique constraint (SLADMIN.XPKSL_PPRJ_TREE) violated
>
> Happens for all rows in the dump. Any suggestions, while I still have
> some hair left?
>

> ~kzm