Oracle Data Pump Complete Guide: Architecture, Usage, SCOTT Schema Setup, and Execution Examples

Oracle Data Pump is a powerful utility for high-speed data export and import operations. This guide provides a complete explanation of how it works, how to use it, and includes hands-on examples using the SCOTT schema.

---

🔧 What is Oracle Data Pump?

Data Pump is a database utility that enables you to export and import data and metadata with great flexibility and speed. It is the replacement for the older exp and imp tools.

It offers the following key commands:

• expdp (Export Data Pump)
• impdp (Import Data Pump)

---

📐 How Data Pump Works

┌──────────────┐        　　  ┌───────────────┐
│　　　　 Data Source 　　　 │ ───▶──▶ │ 　　　Dump File (.dmp) 　　　│
└──────────────┘         　　 └───────────────┘
         ▲               　　　　　　　　　　　　      ▼
         └──────◀──◀──┌──────────────┐
                 　　　　　      │ 　　　　Target DB 　　　　 │
                     　　　　　  └──────────────┘

• expdp writes export files (.dmp) to a directory object
• impdp reads those files and imports them into a database

---

✅ Benefits of Data Pump

Feature	Description
High-speed	Faster than legacy exp/imp
Network export/import	Transfer data between DBs without dump file
Fine-grained control	Export/import specific tables, schemas, etc.
Parallel processing	Significantly improves speed

---

📦 Preparing SCOTT Schema for Testing

Step 1: Create the SCOTT user

CREATE USER scott IDENTIFIED BY tiger;
ALTER USER scott DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE TO scott;

Step 2: Install SCOTT schema tables (emp/dept/etc.)

You can download and run the script utlsampl.sql (usually found under $ORACLE_HOME/rdbms/admin).

@?/rdbms/admin/utlsampl.sql

Introduction to Oracle Database: What is a Sample Schema? How to Set Up a Learning Environment Using the SCOTT Schema

When learning Oracle Database, one of the most effective approaches is hands-on practice using a sample schema.This arti...

oracle-master00000.com

2025.07.28

Step 3: Create directory for Data Pump

CREATE OR REPLACE DIRECTORY dpump_dir AS '/u01/app/oracle/dpdump';
GRANT READ, WRITE ON DIRECTORY dpump_dir TO scott;

---

📤 Exporting SCOTT Schema

expdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=scott.dmp LOGFILE=export.log SCHEMAS=scott

This command exports the SCOTT schema to scott.dmp and logs the operation in export.log.

---

📥 Importing SCOTT Schema

impdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=scott.dmp LOGFILE=import.log SCHEMAS=scott

You can import to another schema or remap tablespaces:

impdp system/password DIRECTORY=dpump_dir DUMPFILE=scott.dmp LOGFILE=import.log REMAP_SCHEMA=scott:newuser

---

🔍 Useful Options

Option	Purpose
CONTENT=DATA_ONLY	Exports only data
CONTENT=METADATA_ONLY	Exports only structure
REMAP_TABLESPACE	Move objects to a different tablespace
REMAP_SCHEMA	Import into another user
INCLUDE/EXCLUDE	Include or exclude specific objects

---

📌 Notes

• You must use a directory object and not a direct file path
• The OS directory must be writable by Oracle
• For large exports, use PARALLEL and ESTIMATE_ONLY for performance tuning

---

🧾 Summary

• Oracle Data Pump is a powerful tool for export/import operations
• Easy to use with expdp and impdp
• Offers flexible control and high performance
• SCOTT schema is ideal for test operations

[reference]
1 Overview of Oracle Data Pump