Friday, March 15, 2013

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!!

LinkWithin

Related Posts Plugin for WordPress, Blogger...