Export/Import problems

Hi all,

I have done a FULL (full=y) export on a db. For the import i need to import into a schema. On the second db, i have dropped the user (cascade) and recreated the user with an empty schema. Now i generated the import with the indexfile option to generate the ddl:

imp system file=expfull.dmp fromuser=test touser=test indexfile=index.sql statistics=none ignore=y

In the index file i removed all the REM comments and replaced the default tablespace with the default tablespace for the user on the second db. I ran the import:

imp system file=expfull.dmp fromuser=test touser=test statistics=none ignore=y

I got the following errors (one of many) duringof the import:

Column 1 3100
Column 2 Pieter Breughelstraat 837
Column 3 3029SK
Column 4 Utrecht
Column 5 Utrecht
Column 6 NL
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (NIK.LOC_C_ID_FK) violated - parent key not foun
d

Now the questions i have are:

Have I done it the wrong way?
Do contraints have to be disabled?
Do i do this by removing the 'enable constraints' bit in the index file generated?

Any help will be much appreciated!

Thanks,
Chucks

why did you use an indexfile as opposed to just letting the import do everything you want?

The import wouldnt enable constraints till the end

I used the indexfile because the user i am importing too has a different tablespace. Once i edited it, i ran the script to create the empty tables et all. Is there another way of doing this?

yes, create the user up front - set up the default tablespaces there and away you go

well that is what i did. I dropped the user and recreated it, however the default tbs is different compared to the db from the export is coming from! Hence the need to edit the export file. Somes tables do get imported however certain rows are not inserted due to constraints!Any ideas?

there is no need to edit anything, the tables will go into the default tablespace of the new user

Davey is right, but if it's that much of pain to have objects in the users default tablespace, why don't you just create new tablespaces with the same names that you had on the old database?

I'm struggling to see the problem here...

Bazza

Hi guys..

The problem i have here is that the users have different tablespaces assigned to them and has to remain this way. During the import i am getting:

IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (FEBSTEST.RPT_PARAMETER_MAP_REPORT_ID_FK) violat

on sevaral of the tables. Hence i that tables rows are not imported! Any solutions guys?

Thanks in advance

No really...davey23uk has already provided a solution to your problem. One question though, do any of the tables you are importing have LOBS? If so, you'll need to precreate these tables.

then why i am getting the contstraint errors?

When you say you are generating the DDL from the indexfile, does this DDL happen to include table creation w/ PRIMARY & FOREIGN keys included? If so...that's the problem.

Yes, i think i have sorted it now. This is what i did:

Disabled all the constraints
trunctated the tables
and re ran the import

it went through ok

Thanks all for your advice

such a backwards / long way to do it

Some process has created and enabled the constraints for that user. Plain and simple. Do it all at once in single statements, ignore the index stuff.

in the destination database (paraphrasing here):

drop user test cascade;

in the source database do the full export.

on the destination system
create user test default tablespace newtablespace
alter user test quota unlimited on newtablespace

imp system file=expfull.dmp fromuser=test touser=test statistics=none ignore=y log=import.log

You shouldn't have any problems if you follow that order and fill in the commands properly.


If that still doesn't work, the you had better pass on the logfiles.