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.