Oracle to Postgres Migration Step by Step: The SH Schema (Partitions, Materialized Views, Bitmap Indexes)

The Oracle SH (Sales History) schema is the standard Oracle example of a star schema data warehouse.

It has a central SALES fact table, six dimension tables, two materialized views, range-partitioned fact tables, and bitmap indexes on low-cardinality columns.

All of those features exist in every real Oracle data warehouse.

And all of them require decisions that a straightforward ora2pg run won't make for you.

This post walks through the exact steps I used to migrate SH from Oracle 19c to PostgreSQL 18 using ora2pg.

The Oracle SH sample schema is available on GitHub.

What SH Contains

SH models a retail sales data warehouse.

Dimension tables: CHANNELS, COUNTRIES, CUSTOMERS, PRODUCTS, PROMOTIONS, TIMES

Fact tables: SALES, COSTS — both range-partitioned by TIME_ID

Materialized views: CAL_MONTH_SALES_MV, FWEEK_PSCAT_SALES_MV

Data volume:

The SALES table at nearly one million rows means the COPY export and load take 15–25 minutes.

Plan for that before starting.

Problem 1: Partitioned Tables Require Two Separate Exports

SALES and COSTS are range-partitioned by TIME_ID in Oracle.

PostgreSQL supports declarative range partitioning with the same semantics.

The problem is that ora2pg splits this into two export types.

The TABLE export generates the parent table DDL — the PARTITION BY RANGE (time_id) declaration — but no child partition tables.

If you load this and try to insert rows, PostgreSQL returns:

ERROR:  no partition of relation "sales" found for row

The partition child tables come from a separate PARTITION export.

You need both files, and you need to load the parent table DDL before the partition child tables.

What this looks like in practice:

-- TABLE export generates:
CREATE TABLE sh.sales (
  prod_id       bigint NOT NULL,
  cust_id       bigint NOT NULL,
  time_id       date   NOT NULL,
  channel_id    bigint NOT NULL,
  promo_id      bigint NOT NULL,
  quantity_sold numeric(10,2) NOT NULL,
  amount_sold   numeric(10,2) NOT NULL
) PARTITION BY RANGE (time_id);

-- PARTITION export generates:
CREATE TABLE sales_1995 PARTITION OF sh.sales
  FOR VALUES FROM (MINVALUE) TO ('1996-01-01');
CREATE TABLE sales_1996 PARTITION OF sh.sales
  FOR VALUES FROM ('1996-01-01') TO ('1997-01-01');
-- ... 33 more partitions

The SH schema has 28 SALES partitions and 9 COSTS partitions — 37 total.

Verify the count matches Oracle before loading.

Problem 2: NUMBER(38) Columns Are Not Handled by PG_INTEGER_TYPE

Every integer ID column in SH is declared as bare NUMBER in Oracle, which stores internally as NUMBER(38).

The ora2pg option PG_INTEGER_TYPE 1 converts bare NUMBER (no precision) to bigint.

But NUMBER(38) has an explicit precision — ora2pg treats it as a numeric type, not an integer, and maps it to numeric(38).

A numeric(38) column works but is wrong for a primary key or foreign key column.

It is 8× larger than bigint, slower to index, and impossible to use with integer-specific operations.

The fix is MODIFY_TYPE — a single-line directive in ora2pg.conf that forces a specific type for specific columns.

Critical rule: MODIFY_TYPE is a single line.

All overrides go on one line, comma-separated.

If you write multiple MODIFY_TYPE lines, ora2pg uses only the first and silently ignores the rest.

Run SHOW_COLUMN first to identify every numeric(38) column:

ora2pg -t SHOW_COLUMN > /home/fernando/ora2pg-sh/output/columns-sh.txt
grep 'numeric(38)' /home/fernando/ora2pg-sh/output/columns-sh.txt

Then add all overrides to ora2pg.conf on one line:

MODIFY_TYPE  CUSTOMERS:CUST_ID:bigint,CUSTOMERS:CUST_CITY_ID:bigint,CUSTOMERS:CUST_STATE_PROVINCE_ID:bigint,CUSTOMERS:CUST_TOTAL_ID:bigint,CUSTOMERS:CUST_SRC_ID:bigint,CHANNELS:CHANNEL_ID:bigint,CHANNELS:CHANNEL_CLASS_ID:bigint,CHANNELS:CHANNEL_TOTAL_ID:bigint,COUNTRIES:COUNTRY_ID:bigint,COUNTRIES:COUNTRY_SUBREGION_ID:bigint,COUNTRIES:COUNTRY_REGION_ID:bigint,COUNTRIES:COUNTRY_TOTAL_ID:bigint,PRODUCTS:PROD_ID:bigint,PRODUCTS:PROD_SUBCATEGORY_ID:bigint,PRODUCTS:PROD_CATEGORY_ID:bigint,PRODUCTS:SUPPLIER_ID:bigint,PRODUCTS:PROD_TOTAL_ID:bigint,PRODUCTS:PROD_SRC_ID:bigint,PROMOTIONS:PROMO_ID:bigint,PROMOTIONS:PROMO_SUBCATEGORY_ID:bigint,PROMOTIONS:PROMO_CATEGORY_ID:bigint,PROMOTIONS:PROMO_TOTAL_ID:bigint,SALES:PROD_ID:bigint,SALES:CUST_ID:bigint,SALES:CHANNEL_ID:bigint,SALES:PROMO_ID:bigint,COSTS:PROD_ID:bigint,COSTS:PROMO_ID:bigint,COSTS:CHANNEL_ID:bigint

Before running any export, confirm there is exactly one active MODIFY_TYPE line:

grep 'MODIFY_TYPE' /etc/ora2pg/ora2pg.conf | grep -v '^\s*#' | wc -l
# Must be: 1

Problem 3: Bitmap Indexes — Keep, Drop, or Replace

Oracle uses bitmap indexes on low-cardinality columns in SH: CHANNEL_ID (5 values), PROMO_ID (503 values), and TIME_ID.

PostgreSQL has no bitmap index type.

By default, ora2pg converts bitmap indexes to GIN indexes (BITMAP_AS_GIN 1).

That is wrong for these columns — GIN is designed for full-text search and array data, not integer foreign keys.

Set BITMAP_AS_GIN 0 in ora2pg.conf.

This tells ora2pg to convert bitmap indexes to regular B-tree indexes, which you then review individually.

The decision table for SH:

BRIN (Block Range Index) is a block-range index that stores min/max values per 128-page block.

For a time series column on a partitioned table, it is far cheaper to maintain than a B-tree and equally effective for range scans.

Edit the TABLE export file before loading:

-- Delete these two lines:
CREATE INDEX sales_channel_bix ON sh.sales (channel_id);
CREATE INDEX costs_channel_bix ON sh.costs (channel_id);

-- Replace:
CREATE INDEX sales_time_bix ON sh.sales (time_id);
-- With:
CREATE INDEX sales_time_bix ON sh.sales USING BRIN (time_id);

-- Replace:
CREATE INDEX costs_time_bix ON sh.costs (time_id);
-- With:
CREATE INDEX costs_time_bix ON sh.costs USING BRIN (time_id);

Problem 4: Materialized View Refresh Is No Longer Automatic

Oracle's CAL_MONTH_SALES_MV and FWEEK_PSCAT_SALES_MV use REFRESH COMPLETE ON DEMAND.

COMPLETE refresh means the view is rebuilt from scratch on each refresh.

PostgreSQL's REFRESH MATERIALIZED VIEW does exactly this — no translation problem.

The problem is scheduling.

Oracle's DBMS_MVIEW.REFRESH and DBMS_SCHEDULER handle automatic refresh.

PostgreSQL has no built-in equivalent.

After migration, the MVs are static snapshots until someone explicitly runs:

REFRESH MATERIALIZED VIEW sh.cal_month_sales_mv;
REFRESH MATERIALIZED VIEW sh.fweek_pscat_sales_mv;

To replicate scheduled refresh, install pg_cron (requires superuser) and create a job after the migration.

There is also a load-order constraint.

PostgreSQL executes the MV SELECT query immediately when you run CREATE MATERIALIZED VIEW AS SELECT.

If you load the MV DDL before the data, the query runs against empty tables and the MV is created empty.

Load data first, then MVs.

There is also a search_path problem.

ora2pg does not add SET search_path to the MVIEW export file.

If you load SH_mviews.sql directly, psql runs as the postgres OS user, whose default search_path is public — the MVs are created in public, not sh.

The fix is the pipe trick:

(echo "SET search_path TO sh;"; cat /home/fernando/ora2pg-sh/output/SH_mviews.sql) | \
  sudo -u postgres psql -d shdb

The echo prepends the SET search_path directive so every CREATE MATERIALIZED VIEW in the file lands in the sh schema.

Problem 5: DIMENSION Objects Are Silently Dropped

Oracle supports CREATE DIMENSION syntax, which defines rollup hierarchies for Oracle's query rewrite engine and OLAP operations.

PostgreSQL has no equivalent.

ora2pg silently skips DIMENSION objects.

There is no warning, no error, no stub DDL.

If you don't check for them manually, you won't know they existed until someone asks why an Oracle-side report that used query rewrite no longer performs as expected.

In the SH schema, CUSTOMERS_DIM, PRODUCTS_DIM, TIMES_DIM, and CHANNELS_DIM are all skipped.

There is no functional impact on standard SQL queries — dimensions are optimizer metadata, not data.

The right action is to document them as dropped in the migration risk register.

Running the Migration: Every Command in Order

Step 1 — Populate Oracle SH Data Using SQLcl

The SH installation script calls sh_populate.sql internally, which uses SQLcl's LOAD command to read CSV files.

SQL*Plus does not support LOAD — it silently skips every LOAD call with SP2-0734: unknown command.

The schema is created but every large table stays at 0 rows.

Download the CSV files first:

# On srv1 (Oracle)
cd /home/oracle/sh

for f in costs customers promotions sales supplementary_demographics times; do
  curl -L -o ${f}.csv \
    "https://raw.githubusercontent.com/oracle-samples/db-sample-schemas/main/sales_history/${f}.csv"
done

ls -lh *.csv
# Expected: 6 files; sales.csv ~74 MB, customers.csv ~13 MB

Run the install using SQLcl, not SQL*Plus:

# On srv1 (Oracle)
cd /home/oracle/sh
sql / as sysdba

Inside SQLcl:

ALTER SESSION SET CONTAINER = pdb1;
@sh_install.sql
-- Prompts:
-- Enter a password for the user SH:             sh
-- Enter a tablespace for SH [USERS]:            (press Enter)
-- Do you want to overwrite the schema? [YES|no]: YES

Verify row counts:

# On srv1 (Oracle)
sqlplus sh/sh@//localhost:1521/pdb1 <<'EOF'
SELECT 'SALES',      COUNT(*) FROM sh.sales    UNION ALL
SELECT 'COSTS',      COUNT(*) FROM sh.costs    UNION ALL
SELECT 'CUSTOMERS',  COUNT(*) FROM sh.customers;
EXIT;
EOF
# Expected: SALES 918843, COSTS 82112, CUSTOMERS 55500
# If any large table is 0: SQLcl was not used — re-run using sql, not sqlplus

Step 2 — Create the PostgreSQL Database and User

# On srv2 (PostgreSQL)
sudo -u postgres psql -c "CREATE USER sh WITH PASSWORD 'sh';"
sudo -u postgres psql -c "CREATE DATABASE shdb OWNER sh;"

sudo -u postgres psql -c "\l shdb"
# Expected: one row, owner sh, encoding UTF8

Step 3 — Create the Output Directory

# On srv2 (PostgreSQL)
mkdir -p /home/fernando/ora2pg-sh/output

Step 4 — Configure ora2pg.conf

# On srv2 (PostgreSQL)
sudo cp /etc/ora2pg/ora2pg.conf /etc/ora2pg/ora2pg.conf.$(date +%Y%m%d)
sudo vi /etc/ora2pg/ora2pg.conf

Set these values:

ORACLE_DSN      dbi:Oracle:host=192.168.0.180;sid=pdb1;port=1521
ORACLE_USER     sh
ORACLE_PWD      sh
SCHEMA          SH
OUTPUT_DIR      /home/fernando/ora2pg-sh/output
CREATE_SCHEMA   1
PG_INTEGER_TYPE 1
PG_NUMERIC_TYPE 1
BITMAP_AS_GIN   0

Step 5 — Run SHOW_COLUMN to Identify NUMBER(38) Columns

# On srv2 (PostgreSQL)
ora2pg -t SHOW_COLUMN > /home/fernando/ora2pg-sh/output/columns-sh.txt
grep 'numeric(38)' /home/fernando/ora2pg-sh/output/columns-sh.txt
# Every column listed here needs a MODIFY_TYPE override to bigint

Step 6 — Set MODIFY_TYPE Overrides

Add the MODIFY_TYPE line to ora2pg.conf (must be one line — see Problem 2 above).

Verify exactly one active line exists:

# On srv2 (PostgreSQL)
grep 'MODIFY_TYPE' /etc/ora2pg/ora2pg.conf | grep -v '^\s*#' | wc -l
# Must be: 1

Step 7 — Export TABLE

# On srv2 (PostgreSQL)
ora2pg -t TABLE -o SH_tables.sql 2>&1 | tee /home/fernando/ora2pg-sh/output/table-export-sh.log

ls -lh /home/fernando/ora2pg-sh/output/SH_tables.sql
# Expected: file present, non-zero size

Step 8 — Fix the TABLE Export (Bitmap Indexes)

# On srv2 (PostgreSQL)
cp /home/fernando/ora2pg-sh/output/SH_tables.sql \
   /home/fernando/ora2pg-sh/output/SH_tables_fixed.sql

vi /home/fernando/ora2pg-sh/output/SH_tables_fixed.sql

Apply the index decisions from Problem 3:

• Delete sales_channel_bix and costs_channel_bix
• Replace sales_time_bix and costs_time_bix with USING BRIN

Step 9 — Export PARTITION

# On srv2 (PostgreSQL)
ora2pg -t PARTITION -o SH_partitions.sql 2>&1 | tee /home/fernando/ora2pg-sh/output/partition-export-sh.log

grep -c 'PARTITION OF' /home/fernando/ora2pg-sh/output/SH_partitions.sql
# Expected: 37 (28 SALES + 9 COSTS)

Step 10 — Export MVIEW

# On srv2 (PostgreSQL)
ora2pg -t MVIEW -o SH_mviews.sql 2>&1 | tee /home/fernando/ora2pg-sh/output/mview-export-sh.log

Step 11 — Export VIEW

# On srv2 (PostgreSQL)
ora2pg -t VIEW -o SH_view.sql 2>&1 | tee /home/fernando/ora2pg-sh/output/view-export-sh.log

Step 12 — Export Data (COPY)

# On srv2 (PostgreSQL)
ora2pg -t COPY -o SH_data.sql 2>&1 | tee /home/fernando/ora2pg-sh/output/copy-export-sh.log
# Expect 10-20 minutes -- SALES is ~918,000 rows
# Monitor in a second terminal: watch -n 10 "wc -l /home/fernando/ora2pg-sh/output/SH_data.sql"

tail -5 /home/fernando/ora2pg-sh/output/SH_data.sql
# Expected: ends with COMMIT; -- if truncated, the export was interrupted, re-run

Step 13 — Load in the Correct Order

Verify the target is empty:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb -c "\dn"
# Expected: no rows (sh schema does not exist yet)

Load 1 — Table DDL (parent tables, indexes, PKs):

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb < /home/fernando/ora2pg-sh/output/SH_tables_fixed.sql
# Expected: CREATE TABLE, CREATE INDEX, ALTER TABLE -- no ERROR lines

sudo -u postgres psql -d shdb -c "\dt sh.*"
# Expected: 9 tables listed

Load 2 — Partition child tables (must come before data):

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb < /home/fernando/ora2pg-sh/output/SH_partitions.sql

sudo -u postgres psql -d shdb -c "
SELECT COUNT(*) FROM pg_tables
WHERE schemaname = 'sh'
  AND (tablename LIKE 'sales_%' OR tablename LIKE 'costs_%');"
# Expected: 37

Load 3 — Data:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb < /home/fernando/ora2pg-sh/output/SH_data.sql
# Single transaction -- if any COPY fails, everything rolls back
# Expect several minutes for SALES

Load 4 — Materialized views (after data, with search_path pipe trick):

# On srv2 (PostgreSQL)
(echo "SET search_path TO sh;"; cat /home/fernando/ora2pg-sh/output/SH_mviews.sql) | \
  sudo -u postgres psql -d shdb

sudo -u postgres psql -d shdb -c "\dm sh.*"
# Expected: cal_month_sales_mv, fweek_pscat_sales_mv, both ispopulated = t

Load 5 — View:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb < /home/fernando/ora2pg-sh/output/SH_view.sql

sudo -u postgres psql -d shdb -c "\dv sh.*"
# Expected: sh | profits | view

Step 14 — Re-Apply FK Constraints

ora2pg's COPY data file drops all FK constraints before loading data and does not re-add them.

This is confirmed ora2pg behaviour (issue #1960).

Verify FKs are missing after the load:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb -c "
SELECT COUNT(*) FROM information_schema.table_constraints
WHERE constraint_schema = 'sh'
  AND constraint_type   = 'FOREIGN KEY';"
# Expected: 0

Re-apply all 10 FKs in one command:

# On srv2 (PostgreSQL)
grep '^ALTER TABLE.*FOREIGN KEY' /home/fernando/ora2pg-sh/output/SH_tables.sql | \
  sudo -u postgres psql -d shdb
# Expected: 10 ALTER TABLE lines, no ERROR lines

Verify:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb -c "
SELECT COUNT(*) FROM information_schema.table_constraints
WHERE constraint_schema = 'sh'
  AND constraint_type   = 'FOREIGN KEY';"
# Expected: 10

What the Final TEST Report Looks Like

Enable PG_DSN in ora2pg.conf, then run:

# On srv2 (PostgreSQL)
ora2pg -t TEST 2>&1 | tee /home/fernando/ora2pg-sh/output/SH_test.txt

Expected diffs (not migration errors):

PRIMARY KEY count mismatch on CHANNELS, COUNTRIES, PRODUCTS (Oracle: 0, PostgreSQL: 1):

Oracle's SH schema defines PKs with DISABLE NOVALIDATE — the constraints are defined but not enforced.

Oracle's catalog reports them as 0 active PKs.

PostgreSQL correctly enforces them.

This is an artefact of the Oracle sample schema design — no action needed.

NOT NULL count mismatch on COSTS and SALES (Oracle: 6 and 7, PostgreSQL: 0):

Both are partitioned tables.

PostgreSQL enforces NOT NULL constraints on partition child tables, not on the parent.

ora2pg's TEST reads the parent and gets 0.

The constraints exist and are enforced — verify with \d sh.sales_1995 if needed.

MVIEW count (2 vs 0):

ora2pg's TEST does not reliably detect materialized views in the target schema.

Verify directly:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb -c "\dm sh.*"
# Expected: both MVs present with ispopulated = t
# If they appear: ignore the TEST counter

Row counts — all tables must match:

CHANNELS                     5  /     5   MATCH
COUNTRIES                   23  /    23   MATCH
CUSTOMERS                55500  / 55500   MATCH
PRODUCTS                    72  /    72   MATCH
PROMOTIONS                 503  /   503   MATCH
TIMES                     1826  /  1826   MATCH
COSTS                    82112  / 82112   MATCH
SALES                   918843  /918843   MATCH
SUPPLEMENTARY_DEMOGRAPHICS 4500 /  4500  MATCH

If SALES shows a mismatch, check individual partition row counts:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb -c "
SELECT tableoid::regclass AS partition, COUNT(*)
FROM   sh.sales
GROUP  BY tableoid::regclass
ORDER  BY tableoid::regclass;"
# Look for partitions with 0 rows -- those indicate a load failure for that time range

In Summary

Most Oracle data warehouse schemas look like SH.

Partitioned fact tables, bitmap indexes on FK columns, materialized views rebuilt on a schedule, and dimension metadata that no one has looked at in years.

None of these are blockers — they all have clean PostgreSQL equivalents.

But the migration is not automatic.

ora2pg gets you 80% of the way there.

The remaining 20% is a set of specific decisions: run TABLE and PARTITION as separate exports and load them in the right order; override every NUMBER(38) column to bigint using MODIFY_TYPE; set BITMAP_AS_GIN 0 and review each index individually; prepend SET search_path when loading materialized views; and re-apply FK constraints after the data load because ora2pg drops them and never puts them back.

The DIMENSION objects are the only thing with no PostgreSQL equivalent.

They are optimizer metadata — dropping them has no effect on query correctness.

Document them as dropped and move on.

In every SH-class migration I run, the same seven issues come up.

Knowing them before the first export is what separates a clean cutover from a day of debugging.

Next Steps

If you have an Oracle data warehouse schema and want to understand the migration scope before committing to a full assessment, start with the free migration audit at rootfan.com/services/.