How to Migrate Oracle Dump Files to PostgreSQL

When migrating from Oracle to PostgreSQL, you often need to extract data from existing .dmp files and load it into PostgreSQL.

This guide walks you through generating PostgreSQL-compatible SQL from .dmp files and importing it — without an Oracle environment.

Migration Overview

.dmp file → OraDB DUMP Viewer → PostgreSQL SQL → psql import

No Oracle installation needed. You'll need:

• OraDB DUMP Viewer (Windows)
• PostgreSQL environment (target)

Step 1: Review Dump Contents

1. Open the .dmp file in OraDB DUMP Viewer
2. Review the table list in the schema tree
3. Check row counts, column structures, and data types for each table

Identify which tables to migrate and exclude any that aren't needed.

Step 2: Export PostgreSQL SQL

Single Table

1. Select a table and choose Export → SQL
2. Select PostgreSQL as the target database
3. Specify the destination and run

Batch Export (Multiple Tables)

1. Select Batch Export from the menu
2. Check the tables to migrate
3. Choose SQL (PostgreSQL) as the output format
4. Specify the output folder and run

This generates CREATE TABLE and INSERT statements in PostgreSQL syntax.

Step 3: Data Type Mapping

OraDB DUMP Viewer automatically converts data types. Here are the key mappings:

Oracle	PostgreSQL	Notes
NUMBER(p,s)	NUMERIC(p,s)	Precision and scale preserved
VARCHAR2(n)	VARCHAR(n)	Direct mapping
CHAR(n)	CHAR(n)	Direct mapping
DATE	TIMESTAMP	Oracle DATE includes time
TIMESTAMP	TIMESTAMP	Direct mapping
CLOB	TEXT	PostgreSQL TEXT is unlimited length
BLOB	BYTEA	Binary data
BINARY_FLOAT	REAL	4-byte floating point
BINARY_DOUBLE	DOUBLE PRECISION	8-byte floating point

Step 4: Import into PostgreSQL

# Create database
createdb mydb

# Import SQL file
psql -d mydb -f exported_table.sql

# Batch import multiple files
for f in /path/to/sql/*.sql; do
  psql -d mydb -f "$f"
done

Step 5: Post-Migration Verification

• Table count: SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';
• Row counts: Compare SELECT count(*) for each table against OraDB DUMP Viewer display
• Data types: Verify column definitions with \d tablename

LOB Data Migration

Tables with BLOB/CLOB data require extra attention:

• CLOB → TEXT: Automatically converted in SQL export
• BLOB → Files: Use LOB extraction to save as files, then import through your application

Alternative: CSV-Based Migration

You can also migrate via CSV instead of SQL:

# After CSV export from OraDB DUMP Viewer
psql -d mydb -c "\COPY tablename FROM '/path/to/table.csv' WITH (FORMAT csv, HEADER true)"

For large datasets, CSV + COPY is faster than INSERT statements.

Summary

With OraDB DUMP Viewer, you can generate PostgreSQL-compatible SQL or CSV from .dmp files without Oracle. Automatic data type conversion minimizes manual script editing.

→ Get your free license