import/export of oracle database

The purpose of this post is to show the important steps, parameters and sample when importing / exporting an Oracle database.

Required parameters:
- USERNAME - database user with administrator right
- PASSWORD - password for the database user
- SERVICE - database schema to import/export
- FILENAME - unique file name for dump file
- TABLE_NAME - database tables' name to export

1. Export database:
EXPDP <USERNAME>/<PASSWORD>@<SERVICE> DUMPFILE=<FILENAME>.DMP

E.g, EXPDP SYSTEM/PASSWORD@XE DUMPFILE=SHOPPING.DMP

2. Export selected tables:
EXPDP <USERNAME>/<PASSWORD>@<SERVICE> DUMPFILE=<FILENAME>.DMP TABLES=<TABLE_NAME_1>, <TABLE_NAME_2>, <TABLE_NAME_N>

E.g, EXPDP SYSTEM/PASSWORD@XE DUMPFILE=SHOPPING.DMP TABLES=USER, STUDENT, EMPLOYEE

3. Import database:
IMPDP <USERNAME>/<PASSWORD>@<SERVICE> DUMPFILE=<FILENAME>.DMP

E.g, IMPDP SYSTEM/PASSWORD@XE DUMPFILE=SHOPPING.DMP

4. Import database (remap existing schema to a new schema):
IMPDP <USERNAME>/<PASSWORD>@<SERVICE> DUMPFILE=<FILENAME>.DMP REMAP_SCHEMA=<EXISTING_SHCEMA>:<NEW_SCHEMA>

E.g, IMPDP SYSTEM/PASSWORD@XE DUMPFILE=SHOPPING.DMP REMAP_SCHEMA=SHOPPING_CART:NEW_SHOPPING_CART


another way to import the existing Oracle database to other Oracle database server is to copy the database with Oracle SQL Developer


Done!!