Oracle imp tables fail due to restraints

0
votes

I am using the Oracle imp utility to import data into a set of empty tables in Oracle 10g. When I try to perform the import much of it fails due to referential integrity constraints. E.g. Can't import data into table A because foreign key in table B does not exist yet.

Here is my import command:

imp C_PLUS/<password> rows=y file=db.dmp ignore=y FROMUSER=C_PLUS 
TOUSER=C_PLUS tables=...

I could manually go through each table on import and when it fails import the missing table first, but I have over 400 tables that need imported and it would take far too long.

Is there a way around this or does anyone have any ideas?

Thanks

2
databaseoracleoracle10gimp
What sort of export do you have (full, tablespace, schema, object)? What sort of import are you trying to do? It sounds like you're trying to import tables one at a time-- is that correct? Is that necessary? Or could you import many tables with one command? – Justin Cave
I have a full database dump file, all I want is the data in the tables for a particular user. The command has a list of all tables, so it's importing them all at once. But it's not doing it in the right order to avoid constraint issues. – BON
Do you want to load all the tables owned by C_PLUS? Or do you want to just load a subset of tables specified by the TABLES parameter? – Justin Cave
I want all the tables owned by C_PLUS. – BON

2 Answers

2
votes

Since you want every table owned by C_PLUS, you can omit the TABLES clause. Just specify the FROMUSER. You can also omit the TOUSER parameter since you're not changing the schema name but that won't affect the behavior of the import. Omitting the TABLES clause should fix the problem with constraints assuming that all the foreign key constraints reference other tables in the same schema and assuming that the data in the export file allows the constraints to be enabled (if the export was done without setting CONSISTENT=Y, there may be orphaned rows in the dump file that will prevent the constraints from being created for example).

0
votes

For each table you can disable the primary key/foreign key constraints before import. Then enable each primary and foreign key constrains after the import.

Disable contraints

ALTER TABLE table_name DISABLE PRIMARY KEY CASCADE;

Enable constraint 

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

If the number of table and the number of constraints are too many to do it manually, you can write scripts to generate the ALTER TABLE commands using the data dictionary views.

Disable PK/FK contraints

SELECT 'ALTER TABLE '||table_name||' DISABLE PRIMARY KEY CASCADE' sql_statement
FROM USER_TABLES;

Enable PK constraint 

SELECT 'ALTER TABLE '||table_name||' ENABLE CONSTRAINT '||constraint_name sql_statement
FROM USER_CONSTRAINTS
where constraint_type ='P';

Enable FK constraint 

SELECT 'ALTER TABLE '||table_name||' ENABLE CONSTRAINT '||constraint_name sql_statement
FROM USER_CONSTRAINTS
where constraint_type ='R';