Oracle Edition-Based Redefinition: Deploying PL/SQL Changes Without a Stop-the-World Window

The deploy that hurts: changing a package everyone calls

Adding an optional parameter to a widely used PL/SQL package specification looks like a five-minute change. In practice it is a scheduling problem. Sessions already running hold parsed code, open cursors and uncommitted work. Replace the package while they are mid-transaction and you get one of two bad outcomes: in-flight calls fail, or two code paths disagree about what the data means while both write to the same tables.

The usual answer is a maintenance window: stop the application, deploy, restart. Edition-Based Redefinition (EBR) is Oracle's long-established alternative. It moves the version decision into the database. A session is pinned to an edition, which is a named, inheritable version of the editioned objects, so new logins can see new code while existing sessions finish against the old code.

That is the thesis worth testing: EBR converts a synchronized stop-the-world event into a drain. It does not remove the coordination, it relocates it.

The three building blocks

Editions are the version containers. Editioning views give the application a stable name (for example, an object called ORDERS) over a real base table, so the physical table can change underneath without the application's SQL changing. Crossedition triggers, marked FORWARD or REVERSE, keep old and new column representations synchronized while both editions are live.

Only editioned object types participate. That is mainly PL/SQL units, views, synonyms and types. Tables themselves are not editioned, which is why column-level changes need the editioning-view plus crossedition-trigger pattern rather than a plain ALTER TABLE.

A worked example: adding a parameter to a shared API

Run these as a schema owner or DBA with the privileges your release requires. Confirm the exact privilege set and syntax against the Edition-Based Redefinition chapter of the Oracle Database Development Guide for your version. The script below has not been executed here; treat it as something to adapt and test in a disposable schema.

-- 1. Create a new edition, a child of the edition this session is using
CREATE EDITION app_v2;

-- 2. Pin this session to it, then compile the new spec and body
ALTER SESSION SET EDITION = app_v2;
-- CREATE OR REPLACE PACKAGE order_api ... (new optional parameter)

-- 3. Confirm which edition this session is actually using
SELECT SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') FROM dual;

-- 4. Once tested, make v2 the landing edition for new sessions
ALTER DATABASE DEFAULT EDITION = app_v2;

Expected checks: step 3 should report the edition you set, not the previous default. After step 4, a brand-new session should report app_v2 while a session opened before step 4 keeps reporting the old edition. That difference is the entire point of the feature, so verify it before you build a release process on it.

Then drain. Existing sessions stay on the old edition until they disconnect or are recycled, which in a connection pool can be a long time. Check whether your release exposes the session's edition in a dynamic performance view; if it does not, track pool recycling and long-running jobs by other means. Only when nothing remains on the old edition should you drop it.

Where EBR stops helping

• It does not make genuinely incompatible data changes safe. If both editions must read and write the same rows with different semantics, you still need a transition window, synchronization logic and a later cleanup step to remove the old representation.
• Editioning views are restricted to simple single-table projections. Joins, aggregates and similar constructs are generally not eligible, so a complex read path may not fit behind one.
• Retiring an edition is a coordination problem, not a command. Pooled connections and long-running jobs can hold an old edition far longer than the deploy itself took.
• EBR is an Enterprise Edition feature. Confirm entitlement before designing a release process around it.

Pilot it, then measure

Start with one internal package in a disposable schema. Confirm the session edition with SYS_CONTEXT before and after the switch, inspect DBA_EDITIONS and DBA_EDITIONING_VIEWS to see what was actually created, and measure how long old sessions really persist in your pool. That number, not the feature list, decides whether EBR buys you a drain or just a slower outage.

Rollback is cheap while the old edition still exists: set the default edition back so new sessions return to the old code, then drop the new edition once no session is using it.