Oracle Data Pump Export Schema with expdp Command Guide

calendar Apr 10, 2026 / · 4 min read · Oracle DataPump expdp Database Backup Schema Export Oracle DBA Database Migration Logical Backup  ·
Share on: linkedin copy

Oracle Data Pump Export Schema with expdp Command Guide

Oracle Data Pump's expdp utility lets you take a fast, flexible logical backup of any schema in your Oracle database. This post breaks down a simple schema-level export command, explains each parameter, and shows you how to run it safely.

What This Command Does

The command below exports all objects and data from the andy schema into a binary dump file stored in an Oracle directory object called my_dir.

1expdp system/****** schemas=andy dumpfile=andy.dp directory=my_dir

This is a schema-mode export — the most common type of logical backup for a single user's data. It captures tables, indexes, views, stored procedures, sequences, constraints, triggers, and all other objects owned by the andy schema.

Breakdown of Each Parameter

ParameterValueMeaning
expdp—Starts the Oracle Data Pump Export utility
system/******CredentialsConnects as the SYSTEM user (password masked); must have EXP_FULL_DATABASE privilege
schemas=andyandyTells Data Pump to export only the andy schema
dumpfile=andy.dpandy.dpName of the output dump file written to the directory object
directory=my_dirmy_dirOracle directory object pointing to the OS folder where the dump file is saved

What Is an Oracle Directory Object?

A directory object is a database alias that maps to a real folder on the server's file system. You do not use an OS path directly in the expdp command. Instead, a DBA creates the directory object first:

1CREATE OR REPLACE DIRECTORY my_dir AS '/u01/oracle/exports';
2GRANT READ, WRITE ON DIRECTORY my_dir TO system;

The DIRECTORY parameter in the export command tells Data Pump to write the dump file and log file into that mapped folder. This separation of the OS path from the command is a security and portability feature of Data Pump.

What Gets Exported

When you run a schema-mode export with schemas=andy, Oracle Data Pump exports the following object types owned by that user:

  • Tables (structure + data)
  • Indexes and constraints
  • Views and materialized views
  • Stored procedures, functions, and packages
  • Sequences and synonyms
  • Triggers
  • Grants and object privileges

How Data Pump Works Internally

When you run expdp, Oracle creates a Master Table inside the exporting user's schema to track job progress and metadata. The utility then uses the internal DBMS_DATAPUMP API to extract data and write it into the binary dump file. Because Data Pump runs server-side (not client-side like the old exp utility), it is significantly faster, especially for large schemas.

Key Points and Best Practices

  • Always use a directory object — never try to pass a raw OS path. The directory must exist on the database server and have correct OS permissions.
  • Run as a privileged user — SYSTEM or any user with the EXP_FULL_DATABASE role can export any schema.
  • Mask your password in scripts — the ****** in the command above is best practice; use a wallet or @ connect identifier in automated jobs to avoid plain-text passwords.
  • Add a log file for debugging — append logfile=andy_export.log to capture any warnings or errors during the export.
  • The .dp extension — Oracle does not enforce a specific extension; .dmp is the common convention, but .dp works fine.
  • schemas= replaces the old owner= — if you used the legacy exp utility, note that SCHEMAS is the modern equivalent of the old OWNER parameter.

Example with Log File Added

A more complete, production-ready version of the command looks like this:

1expdp system/****** schemas=andy dumpfile=andy.dp directory=my_dir logfile=andy_export.log

Adding logfile= writes all job messages, warnings, and completion status to andy_export.log in the same my_dir directory.

Required Privileges

Before running the command, confirm these are in place:

  1. The system user (or your chosen user) has EXP_FULL_DATABASE role
  2. The directory object my_dir exists: SELECT * FROM dba_directories;
  3. Read/Write grants on the directory are assigned to the user running expdp
  4. The OS folder that my_dir points to exists on the database server and is writable by the Oracle OS user

References

Posts in this series