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 → Open DUMP Viewer for Oracle database → PostgreSQL SQL → psql import

No Oracle installation needed. You'll need:

• Open DUMP Viewer for Oracle database (Windows)
• PostgreSQL environment (target)

Step 1: Review Dump Contents

1. Open the .dmp file in Open DUMP Viewer for Oracle database
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

Open DUMP Viewer for Oracle database 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 Open DUMP Viewer for Oracle database 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 Open DUMP Viewer for Oracle database
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 Open DUMP Viewer for Oracle database, 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