{"id":111142,"date":"2026-06-03T12:00:00","date_gmt":"2026-06-03T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=111142"},"modified":"2026-06-02T14:02:46","modified_gmt":"2026-06-02T14:02:46","slug":"why-migrating-from-oracle-is-harder-than-anyone-admits-and-what-you-should-do-instead","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/why-migrating-from-oracle-is-harder-than-anyone-admits-and-what-you-should-do-instead\/","title":{"rendered":"Why migrating from Oracle is harder than anyone admits – and what you should do instead"},"content":{"rendered":"\n

Oracle-to-Postgres migration seems simple at first, but everything changes when you hit the DBMS_* packages. The 200,000 lines of procedural logic your application has built on Oracle’s built-in package ecosystem \u2014 job scheduling, optimizer statistics, transactional messaging, file I\/O, and more \u2014 don’t migrate automatically, and no tool will tell you that until you’re already over budget.<\/strong> Replacing them means reconstructing decades of kernel-level behavior from scratch.<\/strong><\/p>\n\n\n\n

This guide covers which packages cause the most damage, what replacement actually looks like in practice, and how to build a migration plan with honest numbers in it.<\/strong><\/p>\n\n\n\n

Introduction: the common scenario I’ve seen all too often<\/h2>\n\n\n\n

Everyone obsesses over table schemas and indexes\u00a0and, once\u00a0they\u2019re\u00a0finished, they announce their success in migrating the database to the new database platform<\/a>.\u00a0That’s great! However, they fail to mention the 200,000 lines of PL\/SQL wrapped inside DBMS packages that make your database\u00a0work – or how it’ll\u00a0absolutely break<\/em> you\u00a0in the ‘race’ to migrate\u00a0from Oracle<\/a>.\u00a0<\/p>\n\n\n\n

I’ll begin by describing a scene that plays out in\u00a0conference rooms\u00a0across the industry. A scene\u00a0that\u00a0I\u2019ve\u00a0watched for over a decade now.<\/p>\n\n\n\n

It’s quite simple:\u00a0a CTO<\/a> walks in armed with a spreadsheet. On the left column of said spreadsheet are Oracle licensing costs, which are at least\u00a0seven figures, growing annually, and negotiated under duress every renewal cycle.<\/p>\n\n\n\n

On the other side of the spreadsheet: a projected cloud-native PostgreSQL<\/a>\u00a0bill, which is dramatically lower. So, of course, the room agrees this is great. The costs of the upcoming cloud migration<\/a> and planned AI initiatives will be slashed. And just like that, the organization is given the green light to migrate\u00a0away from\u00a0Oracle.\u00a0<\/p>\n\n\n\n

Fast-forward 18 months to 2 years and it’s a different story. The project is massively over budget, the team is exhausted, half the business-critical functionality is broken in ways that only show up under production load, and the organization still has vendor lock-in<\/a>. <\/p>\n\n\n\n

The spreadsheet didn’t lie per se – the numbers it gave were right – but it’s what<\/em> it was measuring that was wrong.<\/p>\n\n\n\n

\n
\n\n

You may also be interested in…<\/strong><\/p>\n\n\n\n

Kellyn’s introduction guide to Oracle (for database professionals<\/a>)<\/p>\n\n\n\n

Podcast: How is Oracle different from other data platforms?<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n

What this article is not<\/em> about<\/h2>\n\n\n\n

I’m not here to talk about table migration, because that part\u00a0was solved a long time ago. Tools like ora2pg<\/a>, AWS Schema Conversion Tool<\/a>, and others can get your DDL (data definition language) across the wire with\u00a0easy\u00a0fidelity. The indexes,\u00a0constraints<\/a>,\u00a0and even foreign keys<\/a>\u00a0come over without a hitch.<\/p>\n\n\n\n

The problem is,\u00a0there\u2019s\u00a0going to be those that signal the\u00a0celebration without realizing what\u00a0hasn\u2019t\u00a0come over and what\u00a0can\u2019t\u00a0come over automatically.\u00a0Oracle has spent four decades baking into its procedural\u00a0DNA\u00a0a very powerful\u00a0and\u00a0complex category\u00a0called\u00a0the\u00a0DBMS_*\u202fpackage ecosystem.\u00a0<\/strong><\/p>\n\n\n\n

The code you can’t read<\/h2>\n\n\n\n

Oracle ships its built-in packages as wrapped, compiled bytecode<\/a> – meaning you can<\/em> call DBMS_SCHEDULER.CREATE_JOB<\/code> and read its documented API, but\u00a0you can\u2019t<\/em> read –\u00a0under any circumstances – its implementation. Effectively, it’s a very beneficial ‘black box’ delivered with your license.\u00a0<\/p>\n\n\n\n

This matters enormously during migration because,\u00a0if your organization has PL\/SQL developers, it’s almost certainly\u00a0built years of\u00a0logic\u202fon top of\u202fthese packages. Every call\u00a0to a\u202fDBMS_*\u202fpackage is a dependency on behavior that Oracle doesn’t document fully,\u00a0with\u00a0performance\u00a0tuned over decades and behavior that your developers have come to rely on in ways they\u00a0don’t\u00a0even consciously recognize.<\/p>\n\n\n\n

There are over 200 DBMS_* – and another 40 or so UTL_*\u00a0packages – available for use in Oracle version 19c\u00a0or newer.\u00a0This article will touch on only a few of these as examples of what challenges a migration may face due to their use.\u00a0<\/strong><\/p>\n\n\n\n

\n
\n
\n
\n

Simple Talk is brought to you by Redgate Software<\/h3>\n
\n Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI. <\/div>\n <\/div>\n Discover how Redgate can help you<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

The first challenge: the code is wrapped<\/h2>\n\n\n\n

The code is wrapped,\u00a0so developers simply can’t see the code ‘under the covers.’\u00a0This is challenge number one.<\/strong><\/p>\n\n\n\n

Here’s an Oracle DBMS\u00a0metadata\u00a0view wrapped package body (and what\u00a0you\u2019ll\u00a0see):<\/p>\n\n\n\n

SQL> SELECT text FROM dba_source WHERE name = 'DBMS_SCHEDULER' AND type = 'PACKAGE BODY'; \n\n \n\nDBMS_SCHEDULER wrapped \n\na000000 \n\n369 \n\nabcd \n\n... \n\n[Binary wrapped content    completely unreadable] \n\n0000000000000000000000000000000000000000 <\/pre><\/div>\n\n\n\n
You\u00a0can\u2019t\u00a0reverse-engineer it\u00a0or\u00a0diff it against a\u00a0PostgreSQL equivalent. You can only read the documentation,\u00a0observe\u00a0the behavior, and\u00a0attempt\u00a0to reproduce it\u00a0in a different platform environment. The\u00a0code is\u00a0functionally, semantically, and performatively\u00a0hidden from anyone outside of\u00a0those internal at Oracle.<\/p>\n\n\n\n
The core problem\u00a0is that\u00a0Oracle’s\u00a0DBMS packages are not just APIs<\/a>, but are\u00a0opaque, compiled executables that interact deeply with the Oracle kernel: the shared memory pool, the\u00a0undo tablespace, the redo log<\/a>, and the scheduler. Replacing them means replacing not just function signatures, but decades of kernel-level integration.\u00a0<\/p>\n\n\n\n
The DBMS_* universe: a taxonomy of pain<\/h2>\n\n\n\n
Specifically, here\u00a0are the most commonly relied-upon Oracle built-in packages. I’ll\u00a0mention what they\u00a0actually do, and why replacing them is genuinely\u00a0hard work.<\/p>\n\n\n\n
DBMS_SCHEDULER\u00a0<\/h3>\n\n\n\n
Enterprise job scheduling with calendaring syntax, job chains, windows, resource groups, and distributed execution.\u00a0<\/p>\n\n\n\n
The facts:\u00a0pg_cron<\/a>\u00a0covers\u00a0maybe 30%\u00a0of this.\u00a0<\/p>\n\n\n\n
DBMS_STATS\u00a0<\/h3>\n\n\n\n
Optimizer statistics gathering, histogram management, pending stats, extended stats, and stat locking.\u00a0<\/p>\n\n\n\n
The facts:\u00a0ANALYZE<\/code> is\u00a0still in its infancy.\u00a0<\/p>\n\n\n\n
DBMS_SQL\u00a0<\/h3>\n\n\n\n
Dynamic SQL<\/a> execution with fine-grained cursor control, bulk binds, and describe capability.\u00a0<\/p>\n\n\n\n
The facts:\u00a0EXECUTE IMMEDIATE<\/code> gaps are non-trivial.<\/p>\n\n\n\n
DBMS_PIPE\u00a0<\/h3>\n\n\n\n
Synchronous and asynchronous inter-session messaging within the database.\u00a0<\/p>\n\n\n\n
The facts:\u00a0no direct PostgreSQL equivalent exists.<\/p>\n\n\n\n
DBMS_ALERT\u00a0<\/h3>\n\n\n\n
Transactional event notification alerts fire only when the triggering transaction commits.\u00a0<\/p>\n\n\n\n
The facts:\u00a0LISTEN<\/code>\/NOTIFY<\/code> lacks transactional binding.<\/p>\n\n\n\n
DBMS_LOB\u00a0<\/h3>\n\n\n\n
Fine-grained LOB<\/a> manipulation: chunked reads, writes, appends,\u00a0substr,\u00a0instr\u00a0on BLOBs and CLOBs.\u00a0<\/p>\n\n\n\n
The facts:\u00a0large objects in PostgreSQL differ semantically<\/em>, so this is an apples-to-oranges comparison.\u00a0<\/p>\n\n\n\n
DBMS_CRYPTO\u00a0<\/h3>\n\n\n\n
Encryption, hashing<\/a>, MAC generation using AES, 3DES, RSA, SHA variants.\u00a0<\/p>\n\n\n\n
The facts:\u00a0pgcrypto<\/a>\u00a0maps well, but not identically. However, it can compete!\u00a0<\/p>\n\n\n\n
DBMS_OUTPUT\u00a0<\/h3>\n\n\n\n
Buffer-based text output from PL\/SQL. Ubiquitous in debugging and reporting procedures.\u00a0<\/p>\n\n\n\n
The facts:\u00a0RAISE NOTICE<\/code> is not semantically equivalent.<\/p>\n\n\n\n
DBMS_UTILITY\u00a0<\/h3>\n\n\n\n
Compilation, dependency analysis, name resolution, comma-string parsing, call stack introspection. <\/p>\n\n\n\n
The facts:\u00a0no single PostgreSQL substitute exists.\u00a0<\/p>\n\n\n\n
DBMS_XMLGEN\u00a0<\/h3>\n\n\n\n
SQL-to-XML conversion with full control over element naming, null<\/a> handling, and row encapsulation.\u00a0<\/p>\n\n\n\n
The facts:\u00a0xmlforest<\/code>\/xmlelement<\/code>\u00a0behavior differs.<\/p>\n\n\n\n
DBMS_PARALLEL_EXECUTE\u00a0<\/h3>\n\n\n\n
Chunk-based parallel DML (data manipulation language) for large table operations with restart capability.\u00a0<\/p>\n\n\n\n
The facts:\u00a0no native equivalent; requires custom design.\u00a0<\/p>\n\n\n\n
UTL_FILE\u00a0<\/h3>\n\n\n\n
Server-side file I\/O, reading and writing OS files from within PL\/SQL.\u00a0<\/p>\n\n\n\n
The facts:\u00a0pg_read_file<\/code>\u00a0is read-only,\u00a0and\u00a0restricted.<\/p>\n\n\n\n
Cases where it gets brutal (case A: DBMS_SCHEDULER)<\/h2>\n\n\n\n
On the surface, DBMS_SCHEDULER<\/a><\/code> looks replaceable. You have jobs?\u00a0Just use pg_cron\u00a0and\u00a0you\u2019re\u00a0done. Except\u00a0it’s\u00a0not done – and experienced teams know that within the first week.\u00a0<\/p>\n\n\n\n
Oracle PL\/SQL\u00a0– a DBMS_SCHEDULER job chain (simplified)\u00a0<\/h4>\n\n\n\n
BEGIN \n\n  DBMS_SCHEDULER.CREATE_PROGRAM( \n\n    program_name   => 'LOAD_DAILY_POSITIONS', \n\n    program_type   => 'STORED_PROCEDURE', \n\n    program_action => 'pkg_etl.load_positions', \n\n    enabled        => TRUE \n\n  ); \n\n \n\n  DBMS_SCHEDULER.CREATE_SCHEDULE( \n\n    schedule_name   => 'TRADING_DAYS_ONLY', \n\n    repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI; \n\n                        BYHOUR=18;BYMINUTE=0; \n\n                        EXCLUDE=NAMED_HOLIDAY_SET:\"NYSE_HOLIDAYS\"' \n\n  ); \n\n \n\n  DBMS_SCHEDULER.CREATE_JOB( \n\n    job_name      => 'POSITION_LOAD_JOB', \n\n    program_name  => 'LOAD_DAILY_POSITIONS', \n\n    schedule_name => 'TRADING_DAYS_ONLY', \n\n    job_class     => 'HIGH_PRIORITY_CLASS', \n\n    enabled       => TRUE \n\n  ); \n\n \n\n  -- Chain: positions must complete before risk calcs run \n\n  DBMS_SCHEDULER.CREATE_CHAIN('RISK_CHAIN'); \n\n  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('RISK_CHAIN','STEP1','LOAD_DAILY_POSITIONS'); \n\n  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('RISK_CHAIN','STEP2','CALC_MARKET_RISK'); \n\n  DBMS_SCHEDULER.DEFINE_CHAIN_RULE( \n\n    chain_name => 'RISK_CHAIN', \n\n    condition  => 'STEP1 COMPLETED', \n\n    action     => 'START STEP2' \n\n  ); \n\nEND; \n\n\/ <\/pre><\/div>\n\n\n\n
Notice what this one job does? It uses a calendaring expression that explicitly excludes NYSE holidays by name. It runs in a named job class with resource allocation, and\u00a0is part of a dependency chain where step 2\u00a0can\u2019t\u00a0begin until step 1 completes.\u00a0That dependency is managed inside<\/em> the database.\u00a0<\/p>\n\n\n\n
With\u202fpg_cron\u202fyou get:\u202fSELECT\u00a0cron.schedule('0 18 * * 1-5', $$CALL\u00a0load_positions()$$);\u00a0<\/code><\/p>\n\n\n\n
That’s\u00a0Monday through Friday at\u00a06pm. It has no concept of market holidays, no job chaining, and no resource classes. To replicate the Oracle behavior fully, you need an external orchestration layer (meaning\u00a0Apache Airflow<\/a>, Prefect<\/a>, Temporal<\/a>)…which\u00a0also\u00a0means your scheduling logic leaves the database.<\/p>\n\n\n\n
This creates problems further down the line, as the scheduling logic now crosses a network boundary and becomes\u00a0 new infrastructure dependency. Dependency that falls onto your team – who now has to operate, monitor, and maintain it.<\/p>\n\n\n\n
Case B: DBMS_STATS<\/h2>\n\n\n\n
We’ll call this one the ‘silent killer.’ It breaks slowly, through query plan degradation that only starts many weeks after go-live, as the data distribution begins to drift from what the optimizer saw at the time of migration.<\/p>\n\n\n\n
\n    
\n\n
You may also be interested in:<\/strong><\/p>\n\n\n\n
6 things to monitor with PostgreSQL<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
Oracle PL\/SQL – fine-grained statistics management\u00a0<\/h4>\n\n\n\n
-- Lock stats on a stable reference table, gather with sampling on a volatile one, \n\n-- then publish as a pending set for review before they affect the optimizer \n\nBEGIN \n\n  DBMS_STATS.GATHER_TABLE_STATS( \n\n    ownname          => 'TRADING', \n\n    tabname          => 'MARKET_PRICES', \n\n    estimate_percent => 15, \n\n    method_opt       => 'FOR ALL COLUMNS SIZE SKEWONLY', \n\n    degree           => 8, \n\n    no_invalidate    => FALSE, \n\n    stattype         => 'DATA' \n\n  ); \n\n \n\n  -- Extended stats on correlated columns the optimizer must understand together \n\n  DBMS_STATS.CREATE_EXTENDED_STATS( \n\n    ownname    => 'TRADING', \n\n    tabname    => 'MARKET_PRICES', \n\n    extension  => '(ASSET_CLASS, INSTRUMENT_TYPE, CURRENCY)' \n\n  ); \n\n \n\n  -- Pending stats: gather without publishing    review plan changes first \n\n  DBMS_STATS.SET_TABLE_PREFS( \n\n    ownname  => 'TRADING', \n\n    tabname  => 'MARKET_PRICES', \n\n    pname    => 'PUBLISH', \n\n    pvalue   => 'FALSE' \n\n  ); \n\nEND; \n\n\/<\/pre><\/div>\n\n\n\n
PostgreSQL’s\u202fANALYZE<\/a><\/code>\u202fis\u00a0coming along nicely,\u00a0but has the following shortfalls:<\/p>\n\n\n\n
No concept of pending statistics.<\/strong><\/p>\n\n\n\n
Lack of column group (extended) statistics configuration of this granularity.<\/strong><\/p>\n\n\n\n
No built-in sampling percentage control per column.<\/strong><\/p>\n\n\n\n
And finally, no mechanism to lock statistics on stable tables while aggressively refreshing volatile ones.<\/strong><\/p>\n\n\n\n
DBAs who spent years tuning Oracle’s optimizer through DBMS_STATS<\/code> will find themselves starting over. And they very often\u00a0won’t\u00a0understand why certain queries that ran in milliseconds in Oracle are now scanning millions of rows in PostgreSQL.\u00a0\u00a0\u00a0<\/p>\n\n\n\n
Case C: DBMS_PIPE and DBMS_ALERT<\/h2>\n\n\n\n
These two packages\u00a0represent\u00a0something that simply does not have a PostgreSQL native equivalent: transactional inter-session communication that lives entirely inside the database.\u00a0<\/p>\n\n\n\n
Oracle PL\/SQL – transactional alert pattern\u00a0<\/h4>\n\n\n\n
-- Session A: inside a transaction, register an alert \n\nBEGIN \n\n  -- This alert fires only if THIS transaction commits \n\n  -- If the transaction rolls back, the alert is never sent \n\n  INSERT INTO orders (order_id, status) VALUES (seq_orders.NEXTVAL, 'PENDING'); \n\n \n\n  DBMS_ALERT.SIGNAL( \n\n    name    => 'NEW_ORDER_ALERT', \n\n    message => 'order_id=' || seq_orders.CURRVAL \n\n  ); \n\n  COMMIT;  -- Alert fires HERE, only because commit succeeded \n\nEND; \n\n\/ \n\n \n\n-- Session B: waiting listener (blocks until alert or timeout) \n\nDECLARE \n\n  l_message VARCHAR2(1800); \n\n  l_status  INTEGER; \n\nBEGIN \n\n  DBMS_ALERT.WAITONE( \n\n    name    => 'NEW_ORDER_ALERT', \n\n    message => l_message, \n\n    status  => l_status, \n\n    timeout => 30 \n\n  ); \n\n  IF l_status = 0 THEN \n\n    process_new_order(l_message); \n\n  END IF; \n\nEND; \n\n\/<\/pre><\/div>\n\n\n\n
The critical property here\u00a0is one\u00a0that\u2019s\u00a0nearly invisible\u00a0until it breaks. The alert is\u00a0transactional\u00a0and it\u00a0fires if – and ONLY if – the\u00a0<\/em>transaction<\/a> commits. Otherwise, a\u00a0rollback<\/a> suppresses it.<\/p>\n\n\n\n
PostgreSQL\u2019s LISTEN<\/code>\/NOTIFY<\/code>\u202fdon’t have this\u00a0guarantee, however, so a\u00a0notification sent inside a transaction still<\/em> gets delivered to listeners even if it’s rolled back. Replicating this behavior is awkward, non-trivial, and most certainly was not part of your migration plan. <\/p>\n\n\n\n
Specifically, it requires building a custom transactional outbox pattern:\u00a0inserting notifications into a table within the transaction, then having a background worker read and dispatch them post-commit.<\/p>\n\n\n\n
PL\/SQL is not<\/em> PL\/pgSQL\u00a0<\/h2>\n\n\n\n
Beyond the DBMS packages, the procedural language itself introduces a category of migration problems that automated tools handle poorly at best.\u00a0<\/p>\n\n\n\n
Oracle PL\/SQL\u00a0– patterns that have no direct PL\/pgSQL\u00a0equivalent\u00a0<\/h4>\n\n\n\n
-- 1.  Autonomous transactions (commit independently of the calling transaction) \n\nCREATE OR REPLACE PROCEDURE log_audit_event(p_event VARCHAR2) AS \n\n  PRAGMA AUTONOMOUS_TRANSACTION; \n\nBEGIN \n\n  INSERT INTO audit_log VALUES (SYSDATE, p_event, SYS_CONTEXT('USERENV','SESSION_USER')); \n\n  COMMIT;  -- Commits WITHOUT committing the outer transaction \n\nEND; \n\n \n\n-- 2.  Package-level state (persists for session lifetime) \n\nCREATE OR REPLACE PACKAGE session_context AS \n\n  g_user_id     NUMBER; \n\n  g_permissions VARCHAR2(4000); \n\n  g_cache       SYS.ODCIVARCHAR2LIST;  -- session-scoped collection \n\n \n\n  PROCEDURE initialize(p_user_id NUMBER); \n\n  FUNCTION  has_permission(p_code VARCHAR2) RETURN BOOLEAN; \n\nEND session_context; \n\n \n\n-- 3.  Bulk collect with LIMIT clause for streaming large result sets \n\nDECLARE \n\n  TYPE t_orders IS TABLE OF orders%ROWTYPE; \n\n  l_orders t_orders; \n\n  CURSOR c_orders IS SELECT * FROM orders WHERE status = 'PENDING'; \n\nBEGIN \n\n  OPEN c_orders; \n\n  LOOP \n\n    FETCH c_orders BULK COLLECT INTO l_orders LIMIT 1000; \n\n    EXIT WHEN l_orders.COUNT = 0; \n\n    FORALL i IN 1..l_orders.COUNT \n\n      INSERT INTO orders_archive VALUES l_orders(i); \n\n    COMMIT;  -- Commit each batch \n\n  END LOOP; \n\nEND; \n\n \n\n-- 4.  Conditional compilation \n\n$IF $$enable_debug $THEN \n\n  DBMS_OUTPUT.PUT_LINE('Debug: entering calc_risk for portfolio ' || p_id); \n\n$END<\/pre><\/div>\n\n\n\n
Each of these patterns, unfortunately, require more than just syntax translation. <\/p>\n\n\n\n
Autonomous<\/a> transactions in PostgreSQL\u00a0require\u00a0a\u202fdblink<\/code>\u202for\u202fpostgres_fdw<\/code> – a network hop to a connection back to the same database<\/em>. This is both architecturally odd and<\/em> has performance implications.\u00a0\u00a0<\/p>\n\n\n\n
Meanwhile, Package-level session state requires rearchitecting around\u202fpg_temp<\/code>\u202fschemas or application-layer caching<\/a>. BULK COLLECT<\/code> \/ FORALL<\/code>\u202f maps imperfectly to PL\/pgSQL’s\u00a0array handling, and also loses some of its performance characteristics in the translation.<\/p>\n\n\n\n
\n
In summary…<\/h3>\n\n\n\n
The migration tools will confidently convert your PL\/SQL to PL\/pgSQL. What they won’t tell you is that the converted code will compile, run, and\u00a0produce wrong answers in production under conditions the automated\u00a0tests never exercised.<\/p>\n\n\n\n
Congratulations!\u00a0You now belong to AWS,\u00a0Google,\u00a0or Azure<\/h2>\n\n\n\n
Here’s where the migration story gets a second,\u00a0underappreciated\u00a0chapter. Organizations rightly worry about Oracle licensing, so they\u00a0run toward\u00a0PostgreSQL because\u00a0it’s open-source<\/a>. This is\u00a0a true statement. <\/strong><\/p>\n\n\n\n
However, “open-source\u00a0database” and “no vendor lock-in” are not<\/em> the same thing – especially\u00a0when your destination is a managed cloud service<\/a><\/strong>.<\/p>\n\n\n\n
What “open-source\u00a0PostgreSQL”\u00a0actually looks\u00a0like at scale on the cloud<\/h4>\n<\/blockquote>\n\n\n\n
-- AWS Aurora PostgreSQL: uses Aurora's distributed storage layer \n\n-- Snapshots, backups, failover all managed by and dependent on AWS \n\n \n\n-- Extensions you've come to rely on: \n\nCREATE EXTENSION aws_s3;          -- AWS-specific.  Binds you to S3. \n\nCREATE EXTENSION aws_lambda;      -- AWS-specific.  Binds you to Lambda. \n\n \n\n-- Google AlloyDB: Postgres-compatible, not Postgres \n\n-- Proprietary columnar engine underneath, custom memory management \n\n-- Performance characteristics differ from vanilla Postgres \n\n \n\n-- Azure Flexible Server: closest to vanilla, but: \n\n--   - Azure AD integration for auth \n\n--   - Private endpoints tied to Azure Virtual Network \n\n--   - Backup\/PITR through Azure Recovery Services \n\n \n\n-- The moment you use ANY of these managed features at scale, \n\n-- your migration cost OUT of that cloud equals your old Oracle exit cost.<\/pre><\/div>\n\n\n\n
Amazon Aurora\u00a0PostgreSQL<\/a>\u00a0is\u00a0not<\/em>\u00a0the same as\u00a0open-source PostgreSQL. Instead, it’s\u00a0a PostgreSQL-compatible interface sitting on top of a proprietary distributed storage engine,\u00a0deeply integrated with AWS<\/a> infrastructure.\u00a0\u00a0<\/p>\n\n\n\n
Its performance profile, failure modes, scaling\u00a0behavior…these\u00a0are AWS’s, not the\u00a0PostgreSQL\u00a0community’s. The same is true of Google’s\u00a0AlloyDB<\/a>, which adds a proprietary columnar acceleration layer. These are excellent products but also<\/em> have\u00a0vendor lock-in,\u00a0just\u00a0dressed in the clothing of open-source.\u00a0<\/p>\n\n\n\n
The extensions trap<\/h2>\n\n\n\n
The moment your team starts using\u202faws_s3, Aurora-specific global database features,\u00a0AlloyDB’s\u00a0columnar store hints, or Azure-specific authentication integrations, you have rebuilt your lock-in dependency.\u00a0You just moved it from an Oracle Enterprise License Agreement to an AWS Enterprise Discount Program negotiation.\u00a0<\/strong><\/p>\n\n\n\n
The\u00a0hyperscalers<\/a>\u00a0are not naive about\u00a0this: their\u00a0managed database services are deliberately designed to be excellent, and to accumulate switching costs naturally through network effects, deep integrations, and proprietary extensions.\u00a0\u00a0<\/p>\n\n\n\n
They win when you migrate to their platform,\u00a0and\u00a0there\u2019s\u00a0a reason\u00a0migration away<\/em> from their platform becomes\u00a0difficult like it is from other platforms, such as Oracle.\u00a0<\/strong><\/p>\n\n\n\n
The spreadsheet that lied<\/h2>\n\n\n\n
Let’s\u00a0revisit that boardroom spreadsheet we mentioned at the start.\u00a0The licensing cost comparison is real – sure,\u00a0Oracle is expensive.\u00a0But the column labeled “savings” is systematically missing several entries:\u00a0<\/p>\n\n\n\n
Cost Category<\/strong> <\/td>Oracle (On-Prem)<\/strong> <\/td>Cloud Managed Postgres<\/strong> <\/td><\/tr>
Database license \/ subscription <\/td>$1,200,000\/yr<\/s> <\/td>$180,000\/yr <\/td><\/tr>
DBA labor (Oracle specialists) <\/td>$420,000\/yr (3 FTE)<\/s> <\/td>$420,000\/yr (same people, new skills needed) <\/td><\/tr>
Migration engineering labor <\/td>   <\/td>$800,000\u20132,400,000 (one-time)<\/strong> <\/td><\/tr>
DBMS_* package replacement <\/td>Included in license <\/td>$300,000\u2013900,000 in custom code<\/strong> <\/td><\/tr>
External orchestration (Airflow, etc.) <\/td>   <\/td>$60,000\u2013200,000\/yr + engineering<\/strong> <\/td><\/tr>
Incident recovery (plan degradation, bugs) <\/td>Low (mature platform) <\/td>High (first 2\u20133 years)<\/strong> <\/td><\/tr>
Cloud compute + storage at scale <\/td>Sunk (existing hardware) <\/td>$400,000\u20131,800,000\/yr<\/strong> <\/td><\/tr>
Cloud egress (data leaving the cloud) <\/td>   <\/td>Variable, sometimes substantial<\/strong> <\/td><\/tr>
Future exit costs (cloud lock-in) <\/td>Known Oracle exit cost <\/td>Unknown, but growing annually<\/strong> <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
This is not an argument against\u00a0migration.\u00a0Indeed, there are times where\u00a0migration genuinely makes sense for teams with smaller Oracle footprints –\u00a0or\u00a0for organizations whose Oracle usage is simple enough that the DBMS_* dependency surface is manageable. Instead, the argument is against doing the math wrong and calling the result a victory.\u00a0<\/p>\n\n\n\n
Shifting Oracle licensing cost to cloud infrastructure cost is not saving money.\u00a0It’s simply moving a number from the left column to the right column of a budget spreadsheet, then presenting the left column to the CFO as evidence of savings…while hoping nobody notices that the right column grew.\u00a0<\/strong><\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Enjoying this article? Subscribe to the Simple Talk newsletter<\/h3>\n                
\n                                            Get selected articles, event information, podcasts and other industry content delivered straight to your inbox.                                    <\/div>\n            <\/div>\n                                            Subscribe now<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
A more honest migration framework (what you should<\/em> do)<\/h2>\n\n\n\n
If your organization is evaluating or mid-stream in an Oracle migration, here is a more grounded approach to the decision: <\/p>\n\n\n\n
Step 1:\u00a0Audit your DBMS_*\u00a0dependency\u00a0first\u00a0<\/em><\/h3>\n\n\n\n
Before any migration decision is\u00a0finalized, run a full dependency audit of every DBMS_* call in your codebase. Not just which<\/em> packages are used, but also\u00a0which specific procedures, which behaviors, and which edge cases your developers have come to rely on. This is not a two-hour query, but more like\u00a0weeks of analysis.\u00a0<\/p>\n\n\n\n
\n    
\n\n
You may also be interested in…<\/strong><\/p>\n\n\n\n
Oracle ASM: Monitoring and Managing (Part 2)<\/a><\/p>\n\n\n\n
How Redgate tools can help with a cloud migration<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
Oracle DBA\u00a0– query to surface DBMS_* dependencies across all schema objects\u00a0<\/h4>\n\n\n\n
SELECT \n\n  d.owner, \n\n  d.name         AS object_name, \n\n  d.type         AS object_type, \n\n  d.referenced_name, \n\n  s.line, \n\n  s.text \n\nFROM \n\n  dba_dependencies  d \n\n  JOIN dba_source   s ON s.owner = d.owner \n\n                      AND s.name = d.name \n\n                      AND s.type = d.type \n\nWHERE \n\n  d.referenced_owner = 'SYS' \n\n  AND d.referenced_name LIKE 'DBMS_%' \n\n  AND d.owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP') \n\nORDER BY \n\n  d.owner, d.name, s.line;<\/pre><\/div>\n\n\n\n
Step 2:\u00a0Classify by ‘replaceability’\u00a0<\/h2>\n\n\n\n
Not all DBMS_* usage is equally\u00a0difficult.\u00a0DBMS_OUTPUT.PUT_LINE<\/code>\u202fis trivially replaced, whereas\u00a0DBMS_SCHEDULER<\/code> job chains with holiday calendars are not. <\/p>\n\n\n\n
So, build a\u00a0tiered inventory: trivial replacements, hard replacements requiring architectural decisions, and functionally irreplaceable behaviors that would require rebuilding application logic from scratch.\u00a0<\/strong><\/p>\n\n\n\n
Step 3:\u00a0Model the full cost (including time-to-risk)\u00a0<\/h2>\n\n\n\n
The risks from an incomplete DBMS_* replacement only surface later down the line – usually in production and under load, in edge cases.\u00a0Budget for a sustained post-migration<\/a> engineering stabilization period\u00a0must be\u00a0realistically\u00a0built,\u00a0including\u00a0two to three years for a complex Oracle workload. <\/p>\n\n\n\n
If that cost makes the business case weaker, so be it. It’s the correct, honest information, and doesn’t need any more explanation than that.<\/strong><\/p>\n\n\n\n
Step 4:\u00a0Evaluate cloud-independent deployment\u00a0<\/h2>\n\n\n\n
If you migrate to PostgreSQL, consider what option gives you better long-term positioning than a full managed, cloud-native service. Would a self-managed PostgreSQL on cloud\u00a0virtual machines (VMs)<\/a> do the job, or does a simple\u00a0on-premises approach suffice?<\/p>\n\n\n\n
I know this may be\u00a0unpopular but, before you swap one lock-in for another,\u00a0make sure you’ve only lost some<\/em> operational convenience.\u00a0If you gain portability and predictable cost, then you can decide if the cloud makes sense after proving it out. For large, stable\u00a0workloads,\u00a0the math often favors the latter.\u00a0<\/strong><\/p>\n\n\n\n
Step 5:\u00a0Consider Oracle alternatives that don’t<\/em> require full migration\u00a0<\/h2>\n\n\n\n
License cost negotiation with Oracle is not fixed. Oracle’s licensing practices are aggressive, but so is\u00a0competition\u00a0and organizations with leverage\u00a0can be persuasive if they are actively\u00a0evaluating alternatives.\u00a0\u00a0<\/p>\n\n\n\n
Most\u00a0find that Oracle’s negotiating floor is significantly lower than their current contract\u00a0and a\u00a0credible migration threat, even a partial one, changes the conversation completely. This sometimes even leads to discounts upwards of 80% for large enterprise customers.\u00a0<\/strong><\/p>\n\n\n\n
The goal is not loyalty to Oracle, but in\u00a0making decisions with accurate data about true total cost. This includes the cost of what you’d have to rebuild, and the cost of the new lock-in\u00a0you\u2019d\u00a0acquire\u00a0on the way out.\u00a0<\/p>\n\n\n\n
The migration that actually works<\/em><\/h2>\n\n\n\n
Successful Oracle migrations\u00a0do\u00a0exist<\/strong>, and they all look quite similar. They took longer than initially planned, were led by engineers with familiarity of both platforms, and there was heavy investment in understanding and replacing DBMS_* behavior before cutting over. And they were honest with leadership about the real<\/em> costs throughout.<\/strong><\/p>\n\n\n\n
What they did not<\/em> do\u00a0is\u00a0treat the database schema as the hard part, because the\u00a0schema is, in fact, the easy<\/em> part.<\/p>\n\n\n\n
The hard part is the 200,000 lines of accumulated behavioral logic that Oracle has made fast, reliable, and invisible over 40 years. The very logic that your application has silently learned to trust.\u00a0<\/p>\n\n\n\n
The DBMS packages are wrapped because Oracle’s IP\u00a0requires\u00a0it. But they are also, in a sense, wrapped in your organization’s institutional knowledge, production incident history, and in the edge cases your DBA patched at 2am. That knowledge is what\u00a0doesn’t<\/em>\u00a0migrate – it\u00a0has to\u00a0be reconstructed, tested, and earned again on the new platform.\u00a0<\/p>\n\n\n\n
Know what\u00a0you’re\u00a0buying when you sign the migration project plan,\u00a0and make sure the right column of that spreadsheet has honest numbers in it.\u00a0<\/strong><\/p>\n\n\n\n
And\u00a0remember:\u00a0there is no such thing as\u00a0a free\u00a0migration. There are only migrations where the true costs show up later, and migrations where you accounted for them honestly from the start.<\/strong><\/p>\n\n\n\n
\n    
\n        
\n            
\n                
How to use Redgate Flyway as a multi-database migration system<\/h3>\n                
\n                                            Learn how to use Flyway to do a single-batch, multi-database migration, comprising SQL Server, Oracle Cloud, PostgreSQL, MySQL and SQLite databases.
\r\n                                    <\/div>\n            <\/div>\n                                            Read the guide<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs: Why migrating from Oracle is harder than anyone admits<\/h2>\n\n                        
1. Why do Oracle-to-PostgreSQL migrations take longer and cost more than projected?<\/h3>\n            
\n                
Most migration estimates focus on schema conversion \u2014 tables, indexes, constraints \u2014 which tooling handles well. The real time sink is Oracle’s DBMS_* package ecosystem: over 200 built-in packages containing job scheduling, statistics management, cryptography, inter-session messaging, and more. These are compiled as unreadable bytecode, deeply integrated with Oracle’s kernel, and have no direct PostgreSQL equivalents. Replacing them requires custom engineering, not just syntax conversion.<\/p>\n            <\/div>\n                    
2. Can automated migration tools like ora2pg handle PL\/SQL conversion?<\/h3>\n            
\n                
They handle DDL well and will convert most PL\/SQL syntax to PL\/pgSQL. The risk is that converted code often compiles and runs, but produces wrong results under production conditions that automated tests don’t cover \u2014 particularly around package-level session state, autonomous transactions, BULK COLLECT behaviour, and any logic that depends on DBMS_* packages whose semantics differ in PostgreSQL.<\/p>\n            <\/div>\n                    
3. Isn't migrating to cloud PostgreSQL a way to avoid vendor lock-in?<\/h3>\n            
\n                
Not automatically. “Open-source database” and “no vendor lock-in” are different things. AWS Aurora PostgreSQL runs on a proprietary distributed storage engine. Google AlloyDB adds a proprietary columnar layer. Once your team adopts cloud-native extensions like aws_s3 or aws_lambda, Aurora global database features, or Azure AD authentication integrations, you have rebuilt lock-in \u2014 just with a different vendor and a different contract to renegotiate.<\/p>\n            <\/div>\n                    
4. How should an organization prepare for an Oracle migration honestly?<\/h3>\n            
\n                
Start with a full DBMS_* dependency audit before any migration decision is finalised \u2014 not which packages are used, but which specific behaviours your developers rely on. Classify each dependency by replaceability: trivial, hard, or architecturally irreplaceable. Model the full cost including external orchestration tooling, post-migration stabilisation (realistically 2\u20133 years for complex workloads), and the new cloud lock-in you’ll accumulate. Only then does the spreadsheet comparison become honest.<\/p>\n            <\/div>\n                    
5. Is staying on Oracle ever the right call?<\/h3>\n            
\n                
Sometimes, yes \u2014 at least as a negotiating position. Oracle’s published licensing costs are not fixed; organizations actively evaluating alternatives frequently achieve significant discounts, sometimes 60\u201380% off, simply by presenting a credible migration plan. The goal is not loyalty to Oracle but making the decision with accurate total-cost data, including what you’d have to rebuild and what new lock-in you’d acquire on the way out.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Oracle-to-Postgres migrations routinely go over budget \u2014 not because of schemas, but because of DBMS_* packages. Learn what breaks, why automated tools miss it, and how to build an honest migration plan.…<\/p>\n","protected":false},"author":316206,"featured_media":108164,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,143533,143534],"tags":[4168,4309,4459,158978],"coauthors":[48576],"class_list":["post-111142","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-oracle-databases","category-postgresql","tag-database","tag-migration","tag-oracle","tag-postgresql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111142","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316206"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=111142"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111142\/revisions"}],"predecessor-version":[{"id":111188,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111142\/revisions\/111188"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/108164"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=111142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=111142"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=111142"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=111142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}