---
title: "PostgreSQL to Oracle migration guide"
description: "Convert a PostgreSQL dump into a Oracle script with the same tables, keys, indexes and data: how to dump, convert, load, and every rule the converter applies."
canonical: https://intosql.com/docs/migrations/postgresql-to-oracle
last_updated: 2026-10-15
---

# PostgreSQL to Oracle migration guide

IntoSQL reads a plain PostgreSQL dump and writes one Oracle script with the same tables, columns, keys, indexes, foreign keys, defaults, identities, sequences, comments and rows. It reads the dump itself, without a database server, and lists in the report every value Oracle can't hold as it is and every object it doesn't carry. The script loads on Oracle 19c and later.

**Convert in the browser:** [PostgreSQL to Oracle](/postgresql-to-oracle) opens the converter set up for this pair.

## Dump for the best result

Any plain dump from `pg_dump` or `pg_dumpall` from PostgreSQL 10 on works, as `.sql` or `.sql.gz`, with `COPY` rows (the default) or `--inserts`:

```shell
pg_dump --no-owner --no-privileges shop | gzip > shop.sql.gz
```

- `--no-owner --no-privileges` leave out roles and grants, which the converter skips anyway.
- A custom or directory archive (`-Fc`, `-Fd`) is binary: `pg_restore -f shop.sql shop.backup` turns it into plain SQL without a server. The converter says so when it gets an archive (`dump_archive`).
- `pg_dumpall` writes every database in one file; each database's tables are named after it, so nothing collides.

A dump made with `--data-only` still converts: the types are read from the values, and the report says the schema was missing (`no_schema`). Partitioned tables arrive as one table with every partition's rows (`partitions_dropped`).

## Convert

In the browser, open [PostgreSQL to Oracle](/postgresql-to-oracle) or the converter on the home page, drop the dump and choose Oracle. It reads the file from your disk in pieces, twice, and writes the script as it goes, so the dump is never uploaded and its size isn't limited by memory. The [desktop app](/docs/desktop) does the same with the file on disk.

With the [command line](/docs/cli):

```shell
intosql inspect shop.sql.gz
intosql convert shop.sql.gz --to oracle --output shop.oracle.sql --report shop.report.json
```

`inspect` lists the tables, their rows and what won't be converted, without writing anything. `convert` writes the script and, with `--report`, the report as JSON.

Through the [API](/docs/api), a dump converts like any other file: open a conversion with its name ending in `.sql` or `.sql.gz` and `"output": {"dialect": "oracle"}`.

## Load into Oracle

```shell
NLS_LANG=.AL32UTF8 sqlplus -S -L shop@//localhost/FREEPDB1 @shop.oracle.sql
```

`NLS_LANG` makes SQL*Plus read the script as UTF-8. The user needs `CREATE TABLE`, `CREATE SEQUENCE`, `CREATE TRIGGER` and `CREATE PROCEDURE`, and quota on its tablespace. The script stops at the first error, creates the tables, copies the rows in blocks of 100 with a commit after each, then adds keys, indexes, foreign keys, identities, sequences and comments. Schemas other than `public` become a prefix on their tables' names (`app.orders` is `APP_ORDERS`). Text is `VARCHAR2` when every value fits 4000 bytes and `CLOB` otherwise (`text_as_lob`).

Oracle stores an empty string as NULL, so values that were `''` read back as NULL; the report counts them per column (`empty_string_null`). Plain names become uppercase (`customers` is `CUSTOMERS`), so queries can leave the quotes out.

## What isn't converted

Views, materialized views, functions, procedures, triggers, rules, row security policies, extensions and types are written in PostgreSQL's own language. They are listed in the report by kind and name, never dropped silently, so you can recreate them in Oracle.


## Data types

[PostgreSQL to Oracle data types](/docs/types/postgresql-to-oracle) lists each type that changes in the script: what it becomes, why, and the report line.

## Every other rule

Generated from the engine, where each rule is decided.

| In PostgreSQL | In Oracle | Why | Report line |
| --- | --- | --- | --- |
| citext | exact comparison | Oracle compares text exactly unless told otherwise, as PostgreSQL's text does; citext columns lose ignoring case. | `case_insensitive_collation` |
| NOT NULL columns with values that become NULL | NOT NULL dropped | A column whose values had to become NULL can't keep NOT NULL, or the load would stop. | `not_null_relaxed` |
| sequences not owned by a column | CREATE SEQUENCE, at the value it had reached | Oracle sequences work the same way. | None |
| generated columns | virtual columns | Oracle computes generated columns on read only; STORED columns become virtual with the same values. | `stored_as_virtual` |
| generated columns with functions Oracle lacks | a plain column with the values | A formula that can't be written for Oracle leaves a plain column with the dump's values and a report line. | `generated_not_translated` |
| defaults with functions Oracle lacks, and defaults from a sequence | no default | A default that can't be written for Oracle is left out and reported. | `default_not_translated` |
| CHECK constraints | translated, inline in CREATE TABLE | Inline checks are tested as each row loads. | None |
| CHECK constraints with functions Oracle lacks | not created | A check that can't be written for Oracle is left out and reported. | `check_skipped` |
| CHECK ... NOT VALID | not created | PostgreSQL never checked the rows that were there before, so they may break it. | `check_not_valid` |
| gin, gist, brin and hash indexes, partial and expression indexes, and indexes on CLOB columns | not created | IntoSQL writes b-tree indexes on columns for Oracle, which can't index a CLOB; create the others again after loading. | `index_skipped` |
| foreign keys | after the data ENABLE NOVALIDATE, then validated with a warning on orphans | The key checks new rows at once and is validated last; rows without a parent leave it unvalidated with a warning instead of stopping the load. | None |
| ON UPDATE CASCADE, SET NULL or SET DEFAULT; ON DELETE SET DEFAULT | dropped | Oracle's foreign keys act on delete only, with CASCADE or SET NULL. | `foreign_key_action_unsupported` |
| foreign keys to tables the dump doesn't create | not created | A key can't name a table that isn't there. | `foreign_key_skipped` |
| names over 128 bytes | shortened | Oracle 12.2 and later names hold 128 bytes, more than PostgreSQL's 63. | `name_shortened` |
| partitioned tables | one table with every partition's rows | The rows are the same; partition the table again in Oracle if it needs it. | `partitions_dropped` |
| schemas | one schema, tables named after their schema first | The script loads into the schema of the user who runs it, so tables of different schemas get their schema's name in front; tables in public keep their names. | `schemas_merged` |
| views, functions, procedures, triggers, rules, policies, extensions and types | listed in the report | Their code is written in PostgreSQL's language, so they are listed for you to recreate. | `not_converted_objects` |
| dumps without CREATE TABLE (--data-only) | types read from the values | A data-only dump has only rows, so the types come from the values. | `no_schema` |
| expressions in INSERT values | NULL | A converter that doesn't run a database can't evaluate them. | `expression_value` |
| text decoded twice (ñ) | copied as it is | The dump holds exactly these characters, so they are copied; the report points them out. | `mojibake_suspected` |