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!