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!

Edition Based Redefinition

This post is a simple example of the problem that can be solved by Oracle’s Edition Based Redefinition (EBR) feature. This example uses a table, a view, a stored procedure and an Oracle job. First up I’ll create the table and the view.

CREATE TABLE demo
( ts timestamp NOT NULL );
CREATE OR REPLACE VIEW demo_view AS
SELECT *
  FROM demo;

At this point the table and view have exactly the same columns.

Next I’ll create a procedure to insert records into the view (be careful to note that I use the view, not the table).

CREATE OR REPLACE PROCEDURE p AS
BEGIN
  INSERT INTO demo_view
  VALUES(systimestamp);
END;

Next I’ll submit an Oracle job to run the procedure over and over at 1 second intervals. This simply simulates application sessions that sign on and create data.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB( job_name => 'J',
                             job_type => 'STORED_PROCEDURE',
                             job_action => 'P',
                             repeat_interval => 'FREQ=SECONDLY;INTERVAL=1',
                             enabled => TRUE );
END;

After a few seconds pass I’ll check the jobs status.

SELECT run_count,
       failure_count
  FROM user_scheduler_jobs;
RUN_COUNT FAILURE_COUNT
---------- -------------
        18             0

This shows the job has run 18 times without failing. At this point there will also be 18 records in the table. And speaking of the table…let’s add a column to it.

SQL> ALTER TABLE demo 2 ADD ( a number );

And check the job again.

RUN_COUNT FAILURE_COUNT
---------- -------------
        53             0

Still no failures because the procedure references the view which remains blissfully unaware of the new column.

Next we cause badness by redefining the view

SQL> CREATE OR REPLACE VIEW demo_view AS
2    SELECT *
3      FROM demo;

At this point the job starts failing. If this was an actual application that would equate to frustrated users.

RUN_COUNT FAILURE_COUNT
---------- -------------
        89            12

The job is fails because the change to the view has invalidated the procedure. The way to stop the badness is to recreate the procedure:

CREATE OR REPLACE PROCEDURE p AS
BEGIN
  INSERT INTO demo_view
  VALUES(systimestamp,
         1); -->> values for new column!
END;

Then we check the jobs a few times and see the failure count is no longer increasing.

RUN_COUNT FAILURE_COUNT
--------- -------------
      123            38
      124            38
      125            38

EBR could be used to avoid the period of badness in this example. In future posts I’ll explain how that is done.

At this point I’d like to show another important point about the data in the table after all of this processing.

SQL> SELECT a,
   2        COUNT(*)
   3  FROM demo
   4 GROUP BY a
   5 ORDER BY a;
         A    COUNT(*)
---------- ----------
         1         68
                   77
2 rows selected.

Records created prior to the change have NULL values for the new column (A). Records created during the period of badness also have NULL values. But the records created after that do have values (1).

I’ll also explain how EBR gets past that as well in future posts.

Thanks for reading.