Showing results for 
Search instead for 
Did you mean: 

export problems ?

Henrique Silva_3
Regular Advisor

export problems ?

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 !!!
"to be or not to be, what was the question ???? "

Re: export problems ?

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."

Jean-Luc Oudart
Honored Contributor

Re: export problems ?

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

fiat lux
Raynald Boucher
Super Advisor

Re: export problems ?

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).
Honored Contributor

Re: export problems ?


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 '||
from user_constraints;

SQL>spool off

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!

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)