How to import just the data without indexes and constraints in Oracle using 'imp' command

Question

We get *.dmp files from client which has some masked table data including indexes and constraints. I do have those table structures (including indexes and constraints) at my end. I want to import just the data without the indexes and constraints (present in the .dmp file) in Oracle10g using 'imp' command.

I am aware of the 'imp' command. Do help me in letting me know the options available in 'imp' command to import only the data.

I have tried using -- rows=yes indexes=no but this does not help.

Answer 1

I am assuming from your post that you already have the tables and ancillary structures in your database, and you just want to suppress the error messages. If that is indeed the case the option you want is IGNORE=Y.

The Oracle documentaion is available online. You don't say what version you're on, but as you're using IMP I'd say 9i was a good fit. Find out more.. (On later versions you should check out DataPump instead).

Answer 2

You should be able to specify indexes=N and constraints=N.

For more info:

%> imp help=y

Here is a link with some good info on the options: Oracle imp information

Answer 3

IMHO IMP can't prevent constraints being applied and triggers being fired, ignore=y only ignores errors that arise. Maybe datapump allows it, I don't know.

So you have to:

1. manually disable all triggers and constraints on imported table
2. do an import with tables=<table names> rows=Y indexes=N constraints=N
3. enable triggers
4. enable validate constraints and resolve any errors (find and edit/remove offending values).

Answer 4

Be careful to use imp version that exactly matches your DB version. I had trouble with this...

Answer 5

Do Ignore=Y. It will ignore the create errors since you have already have the schema.