Oracle Data Pump utilities expdp (export) and impdp (import) facilitate logical backup and migration of database objects using .dmp files. These server-side tools must be executed on the host where the Oracle database resides.

Exporting a Schema to a DMP File

Begin by logging in as the Oracle softawre owner (e.g., oracle). The general syntax for exporting is:

expdp username/password@//host:port/service_name \
  directory=DATA_PUMP_DIR \
  dumpfile=output.dmp \
  logfile=export.log \
  schemas=username \
  content=all

• Replace username/password with valid data base credentials.
• For local exports, omit the host and port; use only the service name or SID.
• directory refers to a pre-defined Oracle directory object. Query dba_directories to locate valid paths:

  SELECT * FROM dba_directories;
  

• dumpfile sets the output filename.
• logfile is optional but recommended for troubleshooting.
• schemas specifies wich schema(s) to export.

Example:

expdp jslt/passwd@orcl \
  directory=DATA_PUMP_DIR \
  dumpfile=jslt_export_20200831.dmp \
  logfile=jslt_export_20200831.log \
  schemas=jslt \
  content=all

The resulting .dmp and .log files appear in the physical path mapped to DATA_PUMP_DIR, typically under $ORACLE_BASE/admin/<SID>/dpdump/.

Importing a DMP File into a Target Database

On the destination server, log in as the Oracle user (su - oracle) and prepare the environment:

1. Connect via SQL*Plus:

   sqlplus /nolog
   SQL> CONNECT / AS SYSDBA
   

2. Create necessary tablespaces and user:

   CREATE TABLESPACE XDBH_DATA
     DATAFILE '/data/oracle/oradata/orcl/XDBH_DATA.DBF'
     SIZE 512M
     AUTOEXTEND ON NEXT 50M
     EXTENT MANAGEMENT LOCAL;
   
   CREATE TEMPORARY TABLESPACE XDBH_TEMP
     TEMPFILE '/data/oracle/oradata/orcl/XDBH_TEMP.DBF'
     SIZE 112M
     AUTOEXTEND ON NEXT 100M
     EXTENT MANAGEMENT LOCAL;
   
   CREATE USER xdbh IDENTIFIED BY xdbh
     DEFAULT TABLESPACE XDBH_DATA
     TEMPORARY TABLESPACE XDBH_TEMP;
   
   ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
   
   GRANT CONNECT, RESOURCE, DBA TO xdbh;
   

3. Place the .dmp file in the target server’s Data Pump directory (verify path via dba_directories).

4. Run the import command:

impdp target_user/target_password@service_name \
  directory=DATA_PUMP_DIR \
  dumpfile=source_file.dmp \
  logfile=import.log \
  remap_schema=source_schema:target_schema \
  remap_tablespace=source_tbs:target_tbs \
  table_exists_action=replace \
  content=all \
  transform=oid:n

Key parameters:

• remap_schema: redirects objects from the original schema to a new one.
• remap_tablespace: maps source tablespaces to target ones.
• table_exists_action=replace: drops and recreates existing tables.
• transform=oid:n: suppresses OID generation for object types.

Example:

impdp jslt/passwd@orcl \
  directory=DATA_PUMP_DIR \
  dumpfile=jslt_export_20200831.dmp \
  logfile=jslt_import_20200831.log \
  remap_schema=jslt:jslt \
  remap_tablespace=LTBH_DATA:JSLT_DATA \
  table_exists_action=replace \
  content=all \
  transform=oid:n