How to export/import the database data

In here, I will show you the basic steps for export/import the data from oracle database 11g.

Please note that the output of the export (expdp) and import (impdp) utilities are not compatible with the original Export(exp) and Import(imp).

For details, please refer to the oracle online document:

https://docs.oracle.com/cd/E18283_01/server.112/e17222/expimp.htm#i262247

Remark: 

To ensure the data exported from the right database instance, please set the ORACLE_SID before you execute the script for export/import.

export ORACLE_SID=dummydb;

A. Sample scripts for the Original Export (exp) and Import (imp) utilities (Compatible with 9i or before).

Export(exp) Utility - export data with specified table:

EXP [USER] TABLES=[TABLE_NAME] FILE=[DUMP_FILE] LOG=[LOG_FILE] FULL=[Y/N] ROWS=[Y/N]

E.g

EXP HR TABLES=EMPLOYEES FILE=exp_hr_employees.dmp log=exp_hr_employees.log full=n rows=y

Import(imp) Utility - Import Data with specified table

IMP [USER] FILE=[EXP_DUMP_FILE_NAME] IGNORE=[Y/N] FROMUSER=[EXPORT_SOURCE_USER] TOUSER=[IMPORT_TARGET_USER] LOG=[LOG_NAME] TABLES=[TABLE_NAME] ROWS=[Y/N] COMMIT=[Y/N]

E.g

IMP SCOTT FILE=exp_hr_employees.dmp IGNORE=Y FROMUSER=HR TOUSER=SCOTT log=imp_hr_employees.log TABLES=EMPLOYEES ROWS=y COMMIT=Y

B. Sample scripts for the Data Pump Utilities (Compatible with 10g or above).

As data dump utility which required to create the directory object on database when perform export/import data process.

The directory object is only a pointer to a physical directory and it does not actually create the physical directory on the file system of the database.So, if the location is changed , we also need update the path in database side.

The directory objects may require to create by DBA if you without sys privilege “CREATE DIRECTORY” on database.

Also, it required the privileges for database user who want to export/import other user data on database.

1. Export Utility

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200

1.1 Export specify table data only

EXPDP [USER] DIRECTORY=[DIRECTORY_OBJECT_NAME] TABLES=[TABLE_NAME] DUMPFILE=[DUMP_FILE] LOGFILE=[LOG_FILE_NAME]

E.g

EXPDP HR DIRECOTRY=DATA_EXP_IMP TABLES=TABLE_A DUMPFILE=impstestexp.dmp LOGFILE=action.log

1.2 Full Export

EXPDP [USER] DIRECTORY=[DIRECTORY_OBJECT_NAME] DUMPFILE=[DUMP_FILE] LOGFILE=[LOG_FILE_NAME]

E.g

EXPDP HR DIRECOTRY=DATA_EXP_IMP DUMPFILE=impstestexp.dmp LOGFILE=action.log

1.3 Full Export with specified schema user

EXPDP [USER] DIRECTORY=[DIRECTORY_OBJECT_NAME] DUMPFILE=[DUMP_FILE] LOGFILE=[LOG_FILE_NAME] SCHEMA=[SCHEMA_NAME]

E.g

EXPDP SYSTEM DIRECOTRY=DATA_EXP_IMP DUMPFILE=impstestexp.dmp LOGFILE=action.log SCHEMA=HR

2. Import Utility

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL300

IMPDP [USER_NAME] DIRECTORY=[DIRETORY_OBJECT_NAME] REMAP_SCHEMA=[EXPORT_SOURCE_USER]:[IMPORT_TARGET_USER] DUMPFILE=[EXPORT_DUMP_FILE] LOGFILE=[LOG_FILE_NAME]

E.g

IMPDP SCOTT DIRECOTRY=DATA_EXP_IMP REMAP_SCHEMA=HR:SCOTT DUMPFILE=impstestexp.dmp LOGFILE=action.log