SUPPLEMENTARY_DEMOGRAPHICS<\/td> 4,500<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nThe SALES table at nearly one million rows means the COPY export and load take 15\u201325 minutes.<\/p>\n\n\n\n
Plan for that before starting.<\/p>\n\n\n\n
Problem 1: Partitioned Tables Require Two Separate Exports<\/h3>\n\n\n\n SALES and COSTS are range-partitioned by TIME_ID in Oracle.<\/p>\n\n\n\n
PostgreSQL supports declarative range partitioning with the same semantics.<\/p>\n\n\n\n
The problem is that ora2pg<\/code> splits this into two export types.<\/p>\n\n\n\nLe TABLE<\/code> export generates the parent table DDL \u2014 the PARTITION BY RANGE (time_id)<\/code> declaration \u2014 but no child partition tables.<\/p>\n\n\n\nIf you load this and try to insert rows, PostgreSQL returns:<\/p>\n\n\n\n
ERROR: no partition of relation \"sales\" found for row<\/code><\/pre>\n\n\n\nThe partition child tables come from a separate PARTITION<\/code> export.<\/p>\n\n\n\nYou need both files, and you need to load the parent table DDL before the partition child tables.<\/p>\n\n\n\n
What this looks like in practice:<\/strong><\/p>\n\n\n\n-- TABLE export generates:\nCREATE TABLE sh.sales (\n prod_id bigint NOT NULL,\n cust_id bigint NOT NULL,\n time_id date NOT NULL,\n channel_id bigint NOT NULL,\n promo_id bigint NOT NULL,\n quantity_sold numeric(10,2) NOT NULL,\n amount_sold numeric(10,2) NOT NULL\n) PARTITION BY RANGE (time_id);\n\n-- PARTITION export generates:\nCREATE TABLE sales_1995 PARTITION OF sh.sales\n FOR VALUES FROM (MINVALUE) TO ('1996-01-01');\nCREATE TABLE sales_1996 PARTITION OF sh.sales\n FOR VALUES FROM ('1996-01-01') TO ('1997-01-01');\n-- ... 33 more partitions\n<\/pre><\/div>\n\n\nThe SH schema has 28 SALES partitions and 9 COSTS partitions \u2014 37 total.<\/p>\n\n\n\n
Verify the count matches Oracle before loading.<\/p>\n\n\n\n
Problem 2: NUMBER(38) Columns Are Not Handled by PG_INTEGER_TYPE<\/h3>\n\n\n\n Every integer ID column in SH is declared as bare NOMBRE<\/code> in Oracle, which stores internally as NOMBRE(38)<\/code>.<\/p>\n\n\n\nThe ora2pg option PG_INTEGER_TYPE 1<\/code> couvre brut NOMBRE<\/code> (no precision) to grand entier<\/code>.<\/p>\n\n\n\nBut NOMBRE(38)<\/code> has an explicit precision \u2014 ora2pg treats it as a numeric type, not an integer, and maps it to num\u00e9rique(38)<\/code>.<\/p>\n\n\n\nA num\u00e9rique(38)<\/code> column works but is wrong for a primary key or foreign key column.<\/p>\n\n\n\nIt is 8\u00d7 larger than grand entier<\/code>, slower to index, and impossible to use with integer-specific operations.<\/p>\n\n\n\nThe fix is MODIFICATION_TYPE<\/code> \u2014 a single-line directive in ora2pg.conf<\/code> that forces a specific type for specific columns.<\/p>\n\n\n\nCritical rule:<\/strong> MODIFICATION_TYPE<\/code> is a single line.<\/p>\n\n\n\nAll overrides go on one line, comma-separated.<\/p>\n\n\n\n
If you write multiple MODIFICATION_TYPE<\/code> lines, ora2pg<\/code> uses only the first and silently ignores the rest.<\/p>\n\n\n\nEx\u00e9cuter SHOW_COLUMN<\/code> first to identify every num\u00e9rique(38)<\/code> column:<\/p>\n\n\n\nora2pg -t SHOW_COLUMN > \/home\/fernando\/ora2pg-sh\/output\/columns-sh.txt\ngrep 'numeric(38)' \/home\/fernando\/ora2pg-sh\/output\/columns-sh.txt\n<\/pre><\/div>\n\n\nThen add all overrides to ora2pg.conf<\/code> on one line:<\/p>\n\n\n\nMODIFY_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\n<\/pre><\/div>\n\n\nBefore running any export, confirm there is exactly one active MODIFICATION_TYPE<\/code> line:<\/p>\n\n\n\ngrep 'MODIFY_TYPE' \/etc\/ora2pg\/ora2pg.conf | grep -v '^\\s*#' | wc -l\n# Must be: 1\n<\/pre><\/div>\n\n\nProblem 3: Bitmap Indexes \u2014 Keep, Drop, or Replace<\/h3>\n\n\n\n Oracle uses bitmap indexes on low-cardinality columns in SH: CHANNEL_ID (5 values), PROMO_ID (503 values), and TIME_ID.<\/p>\n\n\n\n
PostgreSQL has no bitmap index type.<\/p>\n\n\n\n
By default, ora2pg converts bitmap indexes to GIN indexes (BITMAP_AS_GIN 1<\/code>).<\/p>\n\n\n\nThat is wrong for these columns \u2014 GIN is designed for full-text search and array data, not integer foreign keys.<\/p>\n\n\n\n
Ensemble BITMAP_AS_GIN 0<\/code> en ora2pg.conf<\/code>.<\/p>\n\n\n\nThis tells ora2pg to convert bitmap indexes to regular B-tree indexes, which you then review individually.<\/p>\n\n\n\n
The decision table for SH:<\/p>\n\n\n\nOracle bitmap index<\/th> Column cardinality<\/th> PostgreSQL decision<\/th><\/tr><\/thead> sales_channel_bix<\/td> 5 channels<\/td> Drop<\/strong> \u2014 B-tree on 5 values brings no benefit; PostgreSQL uses a sequential scan<\/td><\/tr>sales_cust_bix<\/td> 55,500 customers<\/td> Keep as B-tree<\/strong> \u2014 high cardinality, used for customer-level queries<\/td><\/tr>sales_prod_bix<\/td> 72 products<\/td> Keep as B-tree<\/strong><\/td><\/tr>sales_promo_bix<\/td> 503 promotions<\/td> Keep as B-tree<\/strong><\/td><\/tr>sales_time_bix<\/td> 1,826 days<\/td> Replace with BRIN<\/strong> \u2014 TIME_ID is monotonically ordered on a range-partitioned table<\/td><\/tr>costs_channel_bix<\/td> 5 channels<\/td> Drop<\/strong><\/td><\/tr>costs_prod_bix, costs_promo_bix<\/td> same<\/td> Keep as B-tree<\/strong><\/td><\/tr>costs_time_bix<\/td> ordered dates<\/td> Replace with BRIN<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nBRIN (Block Range Index) is a block-range index that stores min\/max values per 128-page block.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Edit the TABLE export file before loading:<\/p>\n\n\n
\n-- Delete these two lines:\nCREATE INDEX sales_channel_bix ON sh.sales (channel_id);\nCREATE INDEX costs_channel_bix ON sh.costs (channel_id);\n\n-- Replace:\nCREATE INDEX sales_time_bix ON sh.sales (time_id);\n-- With:\nCREATE INDEX sales_time_bix ON sh.sales USING BRIN (time_id);\n\n-- Replace:\nCREATE INDEX costs_time_bix ON sh.costs (time_id);\n-- With:\nCREATE INDEX costs_time_bix ON sh.costs USING BRIN (time_id);\n<\/pre><\/div>\n\n\nProblem 4: Materialized View Refresh Is No Longer Automatic<\/h3>\n\n\n\n Oracle's CAL_MONTH_SALES_MV and FWEEK_PSCAT_SALES_MV use REFRESH COMPLETE ON DEMAND<\/code>.<\/p>\n\n\n\nCOMPLETE refresh means the view is rebuilt from scratch on each refresh.<\/p>\n\n\n\n
PostgreSQL REFRESH MATERIALIZED VIEW<\/code> does exactly this \u2014 no translation problem.<\/p>\n\n\n\nThe problem is scheduling.<\/p>\n\n\n\n
Oracle's DBMS_MVIEW.REFRESH<\/code> et DBMS_SCHEDULER<\/code> handle automatic refresh.<\/p>\n\n\n\nPostgreSQL has no built-in equivalent.<\/p>\n\n\n\n
After migration, the MVs are static snapshots until someone explicitly runs:<\/p>\n\n\n
\nREFRESH MATERIALIZED VIEW sh.cal_month_sales_mv;\nREFRESH MATERIALIZED VIEW sh.fweek_pscat_sales_mv;\n<\/pre><\/div>\n\n\nTo replicate scheduled refresh, install pg_cron (requires superuser) and create a job after the migration.<\/p>\n\n\n\n
There is also a load-order constraint.<\/p>\n\n\n\n
PostgreSQL executes the MV SELECT query immediately when you run CREATE MATERIALIZED VIEW AS SELECT<\/code>.<\/p>\n\n\n\nIf you load the MV DDL before the data, the query runs against empty tables and the MV is created empty.<\/p>\n\n\n\n
Load data first, then MVs.<\/p>\n\n\n\n
There is also a search_path problem.<\/p>\n\n\n\n
ora2pg does not add SET search_path<\/code> to the MVIEW export file.<\/p>\n\n\n\nIf you load SH_mviews.sql<\/code> directly, psql runs as the postgres OS user, whose default search_path is public<\/code> \u2014 the MVs are created in public<\/code>, pas sh<\/code>.<\/p>\n\n\n\nThe fix is the pipe trick:<\/p>\n\n\n
\n(echo "SET search_path TO sh;"; cat \/home\/fernando\/ora2pg-sh\/output\/SH_mviews.sql) | \\\n sudo -u postgres psql -d shdb\n<\/pre><\/div>\n\n\nLe echo<\/code> prepends the SET search_path<\/code> directive so every CREATE MATERIALIZED VIEW<\/code> in the file lands in the sh<\/code> schema.<\/p>\n\n\n\nProblem 5: DIMENSION Objects Are Silently Dropped<\/h3>\n\n\n\n Oracle supports CREATE DIMENSION<\/code> syntax, which defines rollup hierarchies for Oracle's query rewrite engine and OLAP operations.<\/p>\n\n\n\nPostgreSQL has no equivalent.<\/p>\n\n\n\n
ora2pg silently skips DIMENSION objects.<\/p>\n\n\n\n
There is no warning, no error, no stub DDL.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
In the SH schema, CUSTOMERS_DIM, PRODUCTS_DIM, TIMES_DIM, and CHANNELS_DIM are all skipped.<\/p>\n\n\n\n
There is no functional impact on standard SQL queries \u2014 dimensions are optimizer metadata, not data.<\/p>\n\n\n\n
The right action is to document them as dropped in the migration risk register.<\/p>\n\n\n\n
Ex\u00e9cution de la migration\u00a0: chaque commande dans l'ordre<\/h2>\n\n\n\nStep 1 \u2014 Populate Oracle SH Data Using SQLcl<\/h3>\n\n\n\n The SH installation script calls sh_populate.sql<\/code> internally, which uses SQLcl's LOAD<\/code> command to read CSV files.<\/p>\n\n\n\nSQL*Plus does not support LOAD<\/code> \u2014 it silently skips every LOAD<\/code> call with SP2-0734: unknown command<\/code>.<\/p>\n\n\n\nThe schema is created but every large table stays at 0 rows.<\/p>\n\n\n\n
Download the CSV files first:<\/p>\n\n\n
\n# On srv1 (Oracle)\ncd \/home\/oracle\/sh\n\nfor f in costs customers promotions sales supplementary_demographics times; do\n curl -L -o ${f}.csv \\\n "https:\/\/raw.githubusercontent.com\/oracle-samples\/db-sample-schemas\/main\/sales_history\/${f}.csv"\ndone\n\nls -lh *.csv\n# Expected: 6 files; sales.csv ~74 MB, customers.csv ~13 MB\n<\/pre><\/div>\n\n\nRun the install using SQLcl, not SQL*Plus:<\/p>\n\n\n
\n# On srv1 (Oracle)\ncd \/home\/oracle\/sh\nsql \/ as sysdba\n<\/pre><\/div>\n\n\nInside SQLcl:<\/p>\n\n\n
\nALTER SESSION SET CONTAINER = pdb1;\n@sh_install.sql\n-- Prompts:\n-- Enter a password for the user SH: sh\n-- Enter a tablespace for SH [USERS]: (press Enter)\n-- Do you want to overwrite the schema? [YES|no]: YES\n<\/pre><\/div>\n\n\nVerify row counts:<\/p>\n\n\n
\n# On srv1 (Oracle)\nsqlplus sh\/sh@\/\/localhost:1521\/pdb1 <<'EOF'\nSELECT 'SALES', COUNT(*) FROM sh.sales UNION ALL\nSELECT 'COSTS', COUNT(*) FROM sh.costs UNION ALL\nSELECT 'CUSTOMERS', COUNT(*) FROM sh.customers;\nEXIT;\nEOF\n# Expected: SALES 918843, COSTS 82112, CUSTOMERS 55500\n# If any large table is 0: SQLcl was not used \u2014 re-run using sql, not sqlplus\n<\/pre><\/div>\n\n\nStep 2 \u2014 Create the PostgreSQL Database and User<\/h3>\n\n\n\n# On srv2 (PostgreSQL)\nsudo -u postgres psql -c "CREATE USER sh WITH PASSWORD 'sh';"\nsudo -u postgres psql -c "CREATE DATABASE shdb OWNER sh;"\n\nsudo -u postgres psql -c "\\l shdb"\n# Expected: one row, owner sh, encoding UTF8\n<\/pre><\/div>\n\n\nStep 3 \u2014 Create the Output Directory<\/h3>\n\n\n\n# On srv2 (PostgreSQL)\nmkdir -p \/home\/fernando\/ora2pg-sh\/output\n<\/pre><\/div>\n\n\n