Logo ← PostgreSQL Blog

Oracle Data Pump Export and Import

Oracle Data Pump provides a powerful way to export and import data and metadata between Oracle databases. In this comprehensive guide…

Oracle Data Pump Export and Import

Oracle Data Pump provides a powerful way to export and import data and metadata between Oracle databases. In this comprehensive guide, we’ll cover various scenarios and commands to perform Oracle Data Pump export and import operations.

Setting Up Data Pump Directories and Users

Before performing export and import operations, you need to set up Data Pump directories and user permissions:

CREATE DIRECTORY dp_dir AS '/u01/backup';
GRANT READ, WRITE ON DIRECTORY dp_dir TO expuser;
GRANT EXP_FULL_DATABASE TO expuser;
CREATE USER expuser IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO expuser;
GRANT UNLIMITED TABLESPACE TO expuser;

Performing Full Database Export and Import

Full Database Export

To perform a full database export:

EXPDP expuser/password@cbs_yol2 DIRECTORY=dp_dir DUMPFILE=FULL_DB_02.dmp LOGFILE=EXP.log FULL=y EXCLUDE=STATISTICS;

Full Database Import

To perform a full database import:

IMPDP expuser/password@cbs_yol2 DIRECTORY=dp_dir DUMPFILE=FULL_DB_02.dmp LOGFILE=IMP.log REMAP_SCHEMA=old_schema:new_schema EXCLUDE=PROC_SYSTEM_GRANT,USER,TABLESPACE,DIRECTORY FULL=Y;

Performing User and Table-level Export and Import

Exporting a User

To export a specific user:

expdp expuser/password@cbs_yol2 DIRECTORY=dp_dir DUMPFILE=VELI_ARABA_EXPORT.dmp LOGFILE=EXP.log CONTENT=ALL SCHEMAS=veli;

Exporting a Table

To export a specific table:

expdp expuser/password@cbs_yol2 DIRECTORY=dp_dir DUMPFILE=EHB_TABLE.dmp LOGFILE=EXP.log TABLES=EHB;

Exporting Table Metadata Only

To export only the metadata of a table:

expdp expuser/password@cbs_yol2 DIRECTORY=dp_dir DUMPFILE=EHB_META_TABLE.dmp LOGFILE=EXP.log TABLES=EHB CONTENT=METADATA_ONLY;

Excluding Specific Tablespaces

To export all tablespaces except for specific ones:

expdp expuser/password@cbs_yol2 DIRECTORY=dp_dir DUMPFILE=TS_EXPORT.dmp LOGFILE=EXP.log FULL=Y EXCLUDE=TABLESPACE:\"IN \(\'SYSTEM\', \'SYSAUX\'\)\";

Exporting a Specific Tablespace

To export a specific tablespace:

expdp expuser/password@cbs_yol2 DIRECTORY=dp_dir DUMPFILE=NEW_TBS_EXPORT.dmp LOGFILE=EXP.log CONTENT=DATA_ONLY TABLESPACES=NEW_TBS;

Conclusion

Oracle Data Pump provides flexible and powerful tools for exporting and importing data and metadata between Oracle databases. By following the commands and scenarios covered in this comprehensive guide, you can perform a wide range of export and import operations tailored to your specific requirements.