topic Re: export problems ? in Operating System - HP-UX

export problems ?

Henrique Silva_3 — Wed, 18 Sep 2002 14:36:10 GMT

I am doing an export based on schema from a production box that I need to get into a development box.

when trying to import the data, I get an error, because of child/parent relationship. I think the sql is trying to create a child table before its parent exists.

since I am new to oracle tools, could I export the data with constraints=NO, import the data in, then turn constraints ON on the new instance ? Is there a way to export the constraints only ?

We do not have access to the Unix box, and can not simply do a hot backup either. So, it will have to be done via export :-(((

Any help will be appreciated !!!

Henrique Silva

PS.: What is the CONSISTENT option for ? It says cross table consistency !!!

Re: export problems ?

Mattias Johnsson — Wed, 18 Sep 2002 14:51:31 GMT

I can think of a couple of options.
Import the parent table on its own
before the child table
imp file=mydata.dmp tables=parent_table
imp file=mydata.dmp tables=child_table

or do the import without constraints
imp file=mydata.dmp constraints=n rows=y indexes=y
then do the import with constraints only
imp file=mydata.dmp constraints=y rows=n indexes=n

quoting the manual on consistent:
"Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the export command. You should specify CONSISTENT=Y when you anticipate that other applications will be updating the target data after an export has started."

Cheers
/Matt

Re: export problems ?

Jean-Luc Oudart — Wed, 18 Sep 2002 14:54:39 GMT

You should use the consistent option if the database is being updated by other transactions during your export.

Jean-Luc

Re: export problems ?

Raynald Boucher — Thu, 19 Sep 2002 10:55:53 GMT

Looks like you are importing into existing objects.

You could drop the target user and recreate it, a
"user" import (implies a user export) would then recreate all objects automatically with the inter table dependancies implemented at the end.

Your development environment must be quite similar to your production environment though (ie same tablespaces).

Re: export problems ?

Yogeeraj_1 — Thu, 19 Sep 2002 11:18:21 GMT

hi,

Seems like you are importing into existing objects...

I would disable all constraints then reenable them after the import.
==============================
SQL> spool ./disable_constraints.sql
SQL> select 'alter table '||table_name||' disable constraint '||
constraint_name||';'
from user_constraints;

...
SQL>spool off
SQL>@./disable_constraints.sql
==============================

Then you do your import and re-enable the constraints.

NB. IMP with the INDEX=NO so that you don't have problems with the primary key during the first import. That is, import your data then import your indexes.

Hope this helps!

Regards
Yogeeraj