impdp content=data_only, how to specify which tables to import first

Question

I am trying to import my database using the command

impdp SYSTEM/password DIRECTORY=dmpdir DUMPFILE=database.dmp CONTENT=DATA_ONLY

This works at importing some parts of the data, however, I am getting an error:

ORA-02291: integrity constraint (SYSTEM.user_role_user_fk) violated - parent key not found

Basically I need to import data from USER first before I can import data from USER_ROLE

Is there a way to specify order of operation or which table data to import first when using IMPDP?

EDIT: A bad solution I have found is to simply use the same impdp statement as above twice. By doing it twice, I can import both USER and USER_ROLE, however there has to be an easier way to this.

Answer 1

You could disable the constraints before importing, and re-enable them afterwards. You can do that with some dynamic SQL:

begin
  for r in (
    select 'ALTER TABLE "'||c.table_name||
      '" DISABLE CONSTRAINT "'||c.constraint_name||'"' as cmd
    from user_constraints c
    where c.constraint_type = 'R'
  )
  loop
    execute immediate r.cmd;
  end loop;
end;
/

After you've run that, e.g. via SQL*Plus using a heredoc in a shell script, you can then run your impdp command to import all of the tables in one go; and then run the same PL/SQL block but with ENABLE instead of DISABLE.

It's worth checking if you have any disabled constraints before you start. If you do the disable script could skip them based on their status, but the enable script wouldn't be able to tell whether they should be re-enabled or not; so you could either create a static enable script, or hard-code any exceptions you need.