How to Resolve IMP-00029: cannot qualify table name by owner, use FROMUSER parameter

Importing a dump file with table mode by imp, an original import, needs to be carefully handled, especially the table list.

For example, we used to use the list to export (exp) tables.

tables=USER.TABLE, USER.TABLE2, USER.TABLE3
...

IMP-00029

If we used the above table list to import data, it leads to the error IMP-00029.

IMP-00029: cannot qualify table name by owner (USER.TABLE), use FROMUSER parameter

Although we can qualify table names with schema names when exporting (exp). In imp, we need to explicitly use FROMUSER to indicate the schema name instead of qualifying those tables with schema names.

Single Schema

So we use FROMUSER to explicitly specify the schema name.

fromuser=USER
tables=TABLE, TABLE2, TABLE3
...

Multiple Schemas

In other words, for multiple schemas, we need to execute import jobs separately for each schema.

$ imp \"/ as sysdba\" fromuser=USER1 tables=TABLE1, TABLE2
$ imp \"/ as sysdba\" fromuser=USER2 tables=TABLE3, TABLE4

This is how the original import designs. Which means, in table mode:

  • Don't qualify tables.
  • Use FROMUSER.
  • Multiple schemas, separate imports.

Leave a Reply

Your email address will not be published. Required fields are marked *