Manage Learn to apply best practices and optimize your operations.

How to export table from one Oracle database to another

I need to export (using the Oracle exp utility) a table from one Oracle database to another Oracle database.

I need to export (using the Oracle exp utility) a table from one Oracle database (database A schema=abc) to another Oracle database (database B schema=xyz). My login in database A assigns me to schema=123. How can I export this table from database A (schema=abc) and import it into database B (schema=xyz)?

The parameters for export and import are fully documented in the Oracle Utilities Guide.

To export just the table, you can use the following:

exp userid=abc/password file=my_table.dmp tables=my_table

The above will sign on to the database as user "abc" and dump the specified table. To import that table, you can still use the TABLES parameter for the imp utility. But to change the schema owner, you will also need to user the FROMUSER and TOUSER parameters as follows:

imp userid=system/password file=my_table.dmp 
fromuser=abc touser=xyz tables=my_table

When changing schema owners, you will need to run import with a user that has the IMP_FULL_DATABASE role, like the SYSTEM user.

Dig Deeper on Oracle database backup and recovery

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

Nice tips, also you can do it using dbForge Studio for Oracle. This tool allows performing an export from one Oracle database to another just with few clicks, have a look at it Studio for Oracle