-
Foreign Keys while importing a dump
Dear All,
While importing dump into a schema which is already having the tables, how to deal with the foreign key constraints. Its possible to disable all the constraints and then enable it after the import.
Apart from that, is there any other ways to handle it in the import options? I'm using normal imp.exe
Is it possible to do it using data pump?
regards
issac
-
You can either disable all FKs and then enable them back OR -this is what I would do, drop all FKs, import from a dump file created with CONSTRAINTS=N option and then create all FKs with NOVALIDATE option.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
you are not specified what the error got at the time on import...
try this....
You will have to run the structural export with CONSTRAINTS=Y and the data export with CONSTRAINTS=N. Follow these steps:
Run the first export with parameter ROWS=N and parameter CONSTRAINTS=Y.
Run the second export with ROWS=Y and CONSTRAINTS=N.
Run the first import to build the tables with constraints.
Disable all foreign key constraints using ALTER TABLE DISABLE CONSTRAINT
.
5. Run the second import that imports data without building constraints.
6. Enable all foreign key constraints using ALTER TABLE ENABLE CONSTRAINT .
-
Originally Posted by gopu_g
you are not specified what the error got at the time on import...
Poster did not get any error, that's why
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|