Having trouble exporting and importing Oracle tables

sql database oracle impdp
Vampnik picture Vampnik · May 9, 2012 · Viewed 14.5k times · Source

I'm using Oracle 11 and I'm having trouble moving tables between two databases. I've successfully exported a datadumb as follows:

expdp.exe www/www@xe tables=TABLE1,TABLE2,TABLE3 directory=dmpdir dumpfile=tables.dmp

When I try to:

impdp.exe www2/www2@xe tables=TABLE1,TABLE2,TABLE3 directory=dmpdir dumpfile=tables.dmp

As a result I got the following exception:

ORA-39002: invalid operation
ORA-39166: Object WWW2.TABLE1 was not found.
ORA-39166: Object WWW2.TABLE2 was not found.
ORA-39166: Object WWW2.TABLE3 was not found.

If I try to:

impdp.exe www2/www2@xe remap_tables=WWW2:TABLE1,TABLE2,TABLE3 directory=dmpdir dumpfile=tables.dmp

I get:

LRM-00101: unknown parameter name 'remap_tables'

In my situation I can't use a database link. How can I change the schema when importing and exporting my tables? Have I somehow misunderstood Oracle?

Answer

Phil picture Phil · May 9, 2012

Use REMAP_SCHEMA=WWW:WWW2 and drop the tables= clause.

impdp.exe www2/www2@xe REMAP_SCHEMA=WWW:WWW2 directory=dmpdir dumpfile=tables.dmp

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How can I get column names from a table in Oracle?

I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would …

Read More