Oracle 8 min readMay 29, 2025

Oracle Data Pump (expdp/impdp) — Table & Schema Export/Import Guide

Complete guide to Oracle Data Pump for table and schema-level export/import between environments — covers directory setup, parameter files, parallel export, tablespace verification, and schema remapping.

Step 1 — Discover Source & Target Details

Before starting, confirm the source (CMO) and target (FMO) database details:

sql

-- Check DB name, unique name, and role
SELECT name, db_unique_name, database_role, open_mode FROM v$database;

-- Check DB version
SELECT version FROM v$instance;

-- Verify TNS connectivity from both sides
-- Source: tnsping <target_service>
-- Target: tnsping <source_service>

Step 2 — Identify Schema & Table Sizes

Measure what you are exporting before starting — avoids storage surprises:

sql

-- Schema segment size breakdown (useful for multi-schema exports)
SELECT
  ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_GB,
  segment_type,
  owner
FROM dba_segments
WHERE owner IN (
  'HP_PROV_PUB',
  'HP_PROV_SUBSCRIPTIONS',
  'HP_PROV_OUTBOUND_CONFIG',
  'HP_PROV_HOST_CONFIG'
)
GROUP BY segment_type, owner
ORDER BY size_GB DESC;

-- Check if specific tables exist and which schemas own them
SELECT owner, table_name
FROM dba_tables
WHERE table_name IN ('RESELLER','RSLR_CONTRACT_XREF','WEB_RESELLER_HIERARCHY');

-- Total size of a specific schema
SELECT ROUND(SUM(bytes)/1024/1024/1024, 3) "Size in GB"
FROM dba_segments
WHERE owner = 'YOUR_SCHEMA';

💡

Always get the size before export — Data Pump dump files can be 1.5–3× the actual data size before compression.

Step 3 — Set Up Data Pump Directory

Data Pump requires an Oracle directory object pointing to a valid OS path:

sql

-- View existing directories
SET LINES 1234 PAGES 1234
COL owner FORMAT A20
COL DIRECTORY_NAME FORMAT A25
COL DIRECTORY_PATH FORMAT A70
SELECT owner, directory_name, directory_path
FROM dba_directories
ORDER BY owner, directory_name;

-- Create a new directory (as SYSDBA)
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/oracle/backup/datapump/';
CREATE OR REPLACE DIRECTORY PUMP_DIR      AS '/oracle/arch/datapump/';

-- Grant access to the schema user if needed
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO your_schema_user;

💡

The OS directory must exist and be writable by the oracle OS user before creating the directory object.

Step 4 — Export Tables (expdp)

Create a parameter file for the export, then run with nohup:

bash

# Create parameter file
vi /tmp/expdp_tables.par

bash

# expdp_tables.par contents:
directory=DATA_PUMP_DIR
dumpfile=table_backup_%U.dmp
logfile=expdp_tables.log
parallel=4
tables=SCHEMA1.TABLE1,SCHEMA1.TABLE2,SCHEMA2.TABLE3
transform=disable_archive_logging:y

bash

# Run export in background
nohup expdp '/ as sysdba' parfile=/tmp/expdp_tables.par &

# Monitor progress
tail -f /oracle/backup/datapump/expdp_tables.log

💡

parallel=4 uses 4 workers — match this to available CPU/IO capacity.transform=disable_archive_logging:y skips redo generation on the target, significantly speeding up import.

Step 5 — Export Schema (expdp)

bash

# Schema-level export parameter file:
directory=DATA_PUMP_DIR
dumpfile=schema_backup_%U.dmp
logfile=expdp_schema.log
parallel=4
schemas=PROV,HP_PROV_PUB,HP_PROV_SUBSCRIPTIONS

bash

nohup expdp '/ as sysdba' parfile=/tmp/expdp_schema.par &

Step 6 — Import Tables (impdp)

bash

# Standard table import
vi /tmp/impdp_tables.par

bash

# impdp_tables.par contents:
directory=DATA_PUMP_DIR
dumpfile=table_backup_%U.dmp
logfile=impdp_tables.log
parallel=4
tables=PROV.HP_PROV_PUB,PROV.HP_PROV_SUBSCRIPTIONS,PROV.HP_PROV_OUTBOUND_CONFIG,PROV.HP_PROV_HOST_CONFIG

bash

nohup impdp '/ as sysdba' parfile=/tmp/impdp_tables.par &

Step 7 — Import with Table Truncation

When the target table already exists and you want to replace data:

bash

directory=DATA_PUMP_DIR
dumpfile=table_backup_%U.dmp
logfile=impdp_truncate.log
parallel=4
tables=SCHEMA.TABLE_NAME
TABLE_EXISTS_ACTION=TRUNCATE

💡

TABLE_EXISTS_ACTION options: SKIP (default) → skip existing table, REPLACE → drop and recreate, TRUNCATE → truncate then load, APPEND → insert without checking duplicates.

Step 8 — Import with Schema Remapping

When the source and target schema names differ between environments:

bash

directory=DATA_PUMP_DIR
dumpfile=schema_backup.dmp
logfile=impdp_remap.log
tables=SOURCE_SCHEMA.TABLE_NAME
transform=disable_archive_logging:y
REMAP_SCHEMA=SOURCE_SCHEMA:TARGET_SCHEMA

bash

nohup impdp '/ as sysdba' parfile=/tmp/impdp_remap.par &

Step 9 — Tablespace Utilization Verification

After import, verify tablespace usage has not exceeded capacity:

sql

-- Complete tablespace utilization report (permanent + temp)
SET COLSEP |
SET LINESIZE 100 PAGES 100 TRIMSPOOL ON NUMWIDTH 14
COL "Name"     FORMAT A25
COL "Size (GB)" FORMAT A15
COL "Used (GB)" FORMAT A15
COL "Free (GB)" FORMAT A15
COL "(Used) %"  FORMAT A15

SELECT d.status "Status",
       d.tablespace_name "Name",
       TO_CHAR(NVL(a.bytes/1024/1024/1024,0),'99,999,990.90') "Size (GB)",
       TO_CHAR(NVL(a.bytes-NVL(f.bytes,0),0)/1024/1024/1024,'99999999.99') "Used (GB)",
       TO_CHAR(NVL(f.bytes/1024/1024/1024,0),'99,999,990.90') "Free (GB)",
       TO_CHAR(NVL((a.bytes-NVL(f.bytes,0))/a.bytes*100,0),'990.00') "(Used) %"
FROM sys.dba_tablespaces d,
     (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
UNION ALL
-- Temp tablespaces
SELECT d.status "Status",
       d.tablespace_name "Name",
       TO_CHAR(NVL(a.bytes/1024/1024/1024,0),'99,999,990.90') "Size (GB)",
       TO_CHAR(NVL(t.bytes,0)/1024/1024/1024,'99999999.99') "Used (GB)",
       TO_CHAR(NVL((a.bytes-NVL(t.bytes,0))/1024/1024/1024,0),'99,999,990.90') "Free (GB)",
       TO_CHAR(NVL(t.bytes/a.bytes*100,0),'990.00') "(Used) %"
FROM sys.dba_tablespaces d,
     (SELECT tablespace_name, SUM(bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes_cached) bytes FROM v$temp_extent_pool GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.extent_management LIKE 'LOCAL'
  AND d.contents LIKE 'TEMPORARY';

Step 10 — Monitor Data Pump Job Progress

sql

-- Check running Data Pump jobs
SELECT owner_name, job_name, operation, job_mode,
       state, degree, attached_sessions
FROM dba_datapump_jobs
WHERE state = 'EXECUTING';

-- Detailed progress (rows processed, elapsed time)
SELECT job_name, operation, job_mode, state,
       TO_CHAR(start_time,'DD-MON-YY HH24:MI:SS') start_time
FROM dba_datapump_jobs;

💡

You can also attach to a running job interactively: expdp '/ as sysdba' attach=JOB_NAME — then type status at the Export> prompt.

Quick Reference — Common Parameter File Options

bash

# Full database export
full=Y

# Specific schemas
schemas=SCHEMA1,SCHEMA2

# Specific tables
tables=SCHEMA.TABLE1,SCHEMA.TABLE2

# Exclude statistics (speeds up import)
exclude=STATISTICS

# Compression
compression=ALL

# Encryption
encryption=ALL
encryption_password=<password>

# Remap tablespace on import
REMAP_TABLESPACE=SOURCE_TBS:TARGET_TBS

# Remap schema on import
REMAP_SCHEMA=SOURCE_SCHEMA:TARGET_SCHEMA

# Include only specific object types
include=TABLE:"IN ('TABLE1','TABLE2')"

# Query filter during export
query=SCHEMA.TABLE:"WHERE status='ACTIVE'"

# Network import directly from source DB (no dump file needed)
network_link=SOURCE_DB_LINK

All postsOracle · Data Pump · expdp · impdp · Migration