Skip to content
DBAExperts
Oracle Migration & data

How to export and import data in Oracle with Data Pump

Data Pump (expdp/impdp) is the modern way to move data between Oracle databases: full schemas, individual tables, or the whole database.

  1. 1

    Create the logical directory

    Data Pump writes to a DIRECTORY object in the database, not to just any path on the system.

    CREATE DIRECTORY dp_dir AS '/u01/dpump';
GRANT READ, WRITE ON DIRECTORY dp_dir TO system;
  2. 2

    Export a schema

    Generate the dump with a log. Adjust parallel based on the available CPU and disks.

    $ expdp system/clave schemas=ventas \
  directory=dp_dir dumpfile=ventas_%U.dmp \
  logfile=exp_ventas.log parallel=4
  3. 3

    Import into another database or schema

    Use remap_schema to load into a different schema without editing the dump.

    $ impdp system/clave directory=dp_dir \
  dumpfile=ventas_%U.dmp logfile=imp_ventas.log \
  remap_schema=ventas:ventas_qa parallel=4
  4. 4

    Export only specific tables

    To move a few tables, filter with the tables parameter.

    $ expdp system/clave tables=ventas.pedidos,ventas.clientes \
  directory=dp_dir dumpfile=tablas.dmp logfile=exp_tablas.log

// common mistake

Putting the password on the command line leaves it visible in history and in ps. Use a parfile or wallet authentication, and validate versions: you cannot import a dump generated on a version higher than the target.

// when it's worth an expert

In large migrations or ones with limited downtime you have to plan parallelism, network, and data validation. At dba.mx we run Data Pump migrations at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.