EBR – New Edition

In a previous post I introduced a situation where a column was added to a table and view and then a few seconds later a procedure that inserted records into the view was recreated. It all sounds harmless but during that “few seconds” anyone running the procedure would get an error. In this post I’ll show the first step in avoiding that using Edition Based Redefinition (EBR). Let’s jump right into the code shall we…

-- First up we'll add a column to the table. At this point the editioning view in the ORA$BASE Edition will not see
-- the new column so no associated code will be invalidated.
ALTER TABLE demo
ADD ( new_col NUMBER );

-- Then we'll create a brand new edition for the view and make it aware of the new column
SQL> CREATE EDITION new_column;
Edition created.

-- change the current session to use the new edition
SQL> ALTER SESSION SET EDITION = new_column;
Session altered.

-- create a new editioning view that will see the new column - note that the ORA$BASE edition remains
-- unaware of the change
SQL> CREATE OR REPLACE EDITIONING VIEW demo_view AS
 2   SELECT *
 3     FROM demo;
View created.

-- !!! note that without the editions there would be a period of badness here because the procedure
-- !!! recreated below would be marked invalid

-- re-create the procedure to insert into the view with the new column - this version of ths stored
-- procedure is only used in the NEW_COLUMN edition.
SQL> CREATE OR REPLACE PROCEDURE p AS
 2   BEGIN
 3     INSERT INTO demo_view
 4     VALUES(systimestamp,
 5            1);
 6 END;
 7 /
Procedure created.

-- check the views structure (note the new column)
SQL> DESC demo_view
 Name            Null?    Type
 ------------------------ ---------------
 TS              NOT NULL TIMESTAMP(6)
 NEW_COL                  NUMBER

-- execute the procedure
SQL> BEGIN
 2     p;
 3   END;
 4 /
PL/SQL procedure successfully completed.

SQL> COMMIT;
Commit complete.

-- Verify the row created by the prior procedure run by checking the new column
SQL> select count(*)
 2     from demo
 3    where new_col = 1
 4 /

COUNT(*) 
---------- 
         1
1 row selected.

-- check how the job is doing. Its still running in the ORA$BASE edition.
-- all looks good - no failures
SQL> select run_count,
 2          failure_count
 3     from user_scheduler_jobs
 4 /

RUN_COUNT FAILURE_COUNT 
---------- ------------- 
      5549             0
1 row selected.

The view and procedure that are aware of the new column only execute in this edition (NEW_COLUMN)- thus they work fine. The view and code running in the ORA$BASE edition continues to run fine as well. At this point we have two working editions of code. But sooner or later we have to commit to the new one. In the next blog I’ll discuss options to do just that.

Thanks for reading!

EBR – Data Dictionary

Now I’d like to cover some aspects of the Oracle Data Dictionary to explain some details about EBR.

First lets check what edition the current session is working in.

SQL> EXEC DBMS_OUTPUT.PUT_LINE(SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME'));
ORA$BASE

Next lets look at the objects we created in the last post.

SQL> SELECT object_name,
 2          object_type,
 3          editionable,
 4          edition_name
 5     FROM user_objects;

OBJECT_NAME     OBJECT_TYPE             E EDITION_NA
--------------- ----------------------- - ----------
DEMO            TABLE
DEMO_VIEW       VIEW                    Y ORA$BASE
J               JOB
P               PROCEDURE               Y ORA$BASE
SET_EDITION     TRIGGER                 Y ORA$BASE

The view, procedure and trigger are editionable (yay for them!) which so they can differ across editions. The EDITION_NAME column displays the edition they were created in. The other objects (most notably the TABLE) are NOT editionable.

 


	

EBR – Setup

This post introduces 2 key concepts of Edition Based Redefinition (EBR).

Editions

These are versions of database code and table structures. They allow multiple changes such as a new column in a table and all of the code that references it to be made available all at once to an application. Thus the application will not fail due to structure inconsistencies.

Editioning Views

These are Oracle views across editions.

Most of the code below is taken from my previous post so I’ll only add comments for the new EBR related code.

-- Set the EBR Edition that the current Oracle session is to work in. In this case we only
-- have the default Edition of ORA$BASE.
SQL> ALTER SESSION SET EDITION = ORA$BASE;
Session altered.

-- Next we set up a trigger to always use this edition when a user signs on
SQL> CREATE OR REPLACE TRIGGER SET_EDITION
 2   AFTER LOGON
 3   ON SCHEMA
 4   BEGIN
 5     EXECUTE IMMEDIATE 'ALTER SESSION SET EDITION = ORA$BASE';
 6   END;
 7 /
Trigger created.

SQL> CREATE TABLE demo
 2   ( ts timestamp NOT NULL );
Table created.

-- Create an Editioning view on top of the table
SQL> CREATE OR REPLACE EDITIONING VIEW demo_view AS
 2   SELECT *
 3     FROM demo;
View created.

SQL> CREATE OR REPLACE PROCEDURE p AS
 2   BEGIN
 3     INSERT INTO demo_view
 4     VALUES(systimestamp);
 5   END;
 6 /
Procedure created.

SQL> BEGIN
 2     DBMS_SCHEDULER.CREATE_JOB( job_name => 'J',
 3                                job_type => 'STORED_PROCEDURE',
 4                                job_action => 'P',
 5                                repeat_interval => 'FREQ=SECONDLY;INTERVAL=1',
 6                                enabled => TRUE );
 7   END;
 8 /
PL/SQL procedure successfully completed.

SQL> BEGIN
 2     DBMS_SCHEDULER.DROP_JOB('J',TRUE);
 3   END;
 4 /
PL/SQL procedure successfully completed.

Next we check the job log.

SQL> SELECT run_count,
 2          failure_count
 3     FROM user_scheduler_jobs;

RUN_COUNT FAILURE_COUNT
---------- -------------
        17             0

No failures. Next check the table.

SQL> SELECT COUNT(*)
 2     FROM demo;

COUNT(*)
----------
        17

The job is running fine and records are getting into the table.

Thanks for reading!