Edition Based Redefinition for Zero Downtime PL/SQL Deployments

The Challenge of Deploying PL/SQL Changes

In a traditional Oracle deployment, updating a PL/SQL package body invalidates the package and any dependent objects. Active sessions that are mid execution may encounter “ORA-04068: existing state of packages has been discarded” or similar errors. For applications that must operate continuously, such as healthcare systems, financial platforms, or large scale web applications, this disruption is unacceptable.

What Edition Based Redefinition Offers

Edition Based Redefinition (EBR) allows you to maintain multiple versions of PL/SQL objects (packages, procedures, functions, triggers, views, and synonyms) simultaneously in the same database. Existing sessions continue using the old edition while new sessions connect to the new edition. Once all old sessions have completed, the old edition can be retired.

Setting Up Editions

-- Create a new edition (requires DBA privileges)
CREATE EDITION release_2024_q4;

-- Grant usage to your application schema
GRANT USE ON EDITION release_2024_q4 TO app_schema;

-- Enable editioning for the schema
ALTER USER app_schema ENABLE EDITIONS;

-- Make specific object types editionable
ALTER USER app_schema ENABLE EDITIONS FOR TYPE;
ALTER USER app_schema ENABLE EDITIONS FOR PACKAGE;
ALTER USER app_schema ENABLE EDITIONS FOR FUNCTION;
ALTER USER app_schema ENABLE EDITIONS FOR PROCEDURE;
ALTER USER app_schema ENABLE EDITIONS FOR TRIGGER;
ALTER USER app_schema ENABLE EDITIONS FOR VIEW;

Deploying a Change

-- Switch to the new edition for your deployment session
ALTER SESSION SET EDITION = release_2024_q4;

-- Now modify your package. This only affects the new edition.
CREATE OR REPLACE PACKAGE BODY order_processing AS

   PROCEDURE submit_order(p_order_id IN NUMBER) IS
   BEGIN
      -- New improved logic in release_2024_q4
      validate_order(p_order_id);
      calculate_pricing_v2(p_order_id);  -- new pricing engine
      apply_discounts(p_order_id);
      finalize_order(p_order_id);
   END submit_order;

END order_processing;
/

-- Verify it compiled cleanly in this edition
SELECT object_name, status
  FROM user_objects
 WHERE object_name = 'ORDER_PROCESSING'
   AND object_type = 'PACKAGE BODY';

How Sessions See Different Editions

Sessions that were established before the deployment continue running against the original edition. They see the old package body and execute the old logic. New sessions that connect after the deployment (or that explicitly set their edition) see the new package body. Both versions coexist in the database without conflict.

You can configure Oracle to set the edition for new sessions based on a service name, which makes it easy to direct traffic to the new edition gradually using connection pool configuration or load balancer rules.

Cross Editioning Views for Table Changes

EBR becomes more complex when your deployment involves table structure changes (adding or removing columns). Since tables are not editionable, you use cross edition triggers and editioning views to present different table structures to different editions. The old edition sees the table through a view that matches the old column layout, while the new edition sees it through a view that includes the new columns.

Practical Advice

EBR is powerful but adds complexity to your deployment process. It is best suited for environments where zero downtime is a hard requirement and where you have a disciplined release process. For most applications that can tolerate a brief maintenance window, simpler deployment strategies (such as deploying during low traffic periods) may be more practical. Evaluate EBR against your actual availability requirements before committing to it.

Version Notes

EBR was introduced in Oracle 11g Release 2 and has been progressively improved. Oracle 12c added support for editioning views on tables with virtual columns. Oracle 19c improved the handling of cross edition triggers for complex table transformations. In 23ai, the documentation and tooling around EBR have been enhanced, but the core mechanism remains unchanged. APEX applications can be configured to use specific editions, making EBR compatible with APEX deployments.