Import specific tables from oracle dump file?

Question

I have a dump of a huge oracle database so it is impossible to import it all. I want to import a specific table called X. The problem is that X has foreign keys. If I import just X, I will get the following error:

imp user/pass@dbName tables=X  rows=y ignore=Y
ORA-02291: integrity constraint violated - parent key not found

I already have the whole db locally (but without data), I want to import all tables that are associated to X. How can I achieve that? I have plsql installed. I also need to know the order of these tables to know which to import at first.

Answer 1

You dan disable all DB constraints before the import, and re-enable them afterwards. See:

• disable-all-table-constraints-in-oracle or
• oracle_disable_constraints

Answer 2

There are few questions so I will try to answer one by one.

ORA-02291: integrity constraint violated - parent key not found

No brainer to guess this because as you know you don't have parent record for table X. By the way you may also want to use flag CONSTRAINTS=N because you already have db as you said.

"I want to import all tables that are associated to X. How can I achieve that?"

Well no option but to find all the dependencies manually (or use data dictionary tables user_cons_columns, user_constraints etc to lookup) and import those tables as well. Think of it if you don't do that. You will break your data integrity. If you still want that data in table X without dependencies then disable the constraints and then import. But you won't be enable your constraint again and I don't know what you want to do with broken data.

"I also need to know the order of these tables to know which to import at first."

Disable the constraints before import and then enable them after import. You don't have to worry about order in that case.