Edition-Based Redefinition in Oracle Database 11g Release 2

Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema, which makes it possible to perform upgrades of database applications with zero down time.

There is a lot of detail in the documentation about this subject and I'm intentionally going to ignore most of it to keep this article as brief as possible. Instead I will give a little background information, then focus on two examples of its use to give you an idea of what it is capable of.

Editions
Enabling Editions for a User
Editionable Objects
Editioning Views
Crossedition Triggers
Dictionary Views
Preparing an Existing Application for Editioning
Example 1: No Data Transformation
Example 2: Two-Way Data Transformation
Switching Between Editions
Services and Editions (11.2.0.2 Update)

Editions

An edition is effectively a version label that can be assigned to all editionable objects in a schema. When a new edition is used by a schema, all editionable objects are inherited by the new edition from the previous edition. These objects can subsequently then be altered or dropped as desired, but doing so will stop inheritance of that object.

From Oracle database 11g release 2 onwards, each database has at least one edition, the default being ORA$BASE. The default edition can be displayed using the DATABASE_PROPERTIES view.

CONN / AS SYSDBA

SELECT property_value
FROM   database_properties
WHERE  property_name = 'DEFAULT_EDITION';

PROPERTY_VALUE
--------------------------------------------------------------------------------
ORA$BASE

SQL>

The CREATE ANY EDITION and DROP ANY EDITION privileges are required to create and drop editions, so edition management is best done from privileged users.

Editions are created using the CREATE EDITION command with an optional AS CHILD OF clause.

CREATE EDITION edition-name;
CREATE EDITION edition-name AS CHILD OF parent-edition;

If the AS CHILD OF is omitted, the parent is assumed to be the leaf edition. In 11gR2 an edition can only have a single child, so this clause is not needed, but hints that editions with multiple children will be possible in future. Attempting to create an edition with multiple children results in the following error.

ORA-38807: Implementation restriction: an edition can have only one child

The DBA_EDITIONS view displays all database editions.

CREATE EDITION release_v1;
CREATE EDITION release_v2 AS CHILD OF release_v1;
CREATE EDITION release_v3;
CREATE EDITION release_v4;

SELECT * FROM dba_editions;

EDITION_NAME		       PARENT_EDITION_NAME	      USA
------------------------------ ------------------------------ ---
ORA$BASE						      YES
RELEASE_V1		       ORA$BASE 		      YES
RELEASE_V2		       RELEASE_V1		      YES
RELEASE_V3		       RELEASE_V2		      YES
RELEASE_V4		       RELEASE_V3		      YES

SQL>

The default database edition can be switched using the ALTER DATABASE command.

ALTER DATABASE DEFAULT EDITION = edition-name;

Only leaf editions can be dropped. Attempting to drop an edition in the middle of the chain results in the following error.

ORA-38810: Implementation restriction: cannot drop edition that has a parent and a child

The following code shows that dropping the leaf edition works as expected.

DROP EDITION release_v4;

SELECT * FROM dba_editions;

EDITION_NAME                   PARENT_EDITION_NAME            USA
------------------------------ ------------------------------ ---
ORA$BASE						      YES
RELEASE_V1		       ORA$BASE 		      YES
RELEASE_V2		       RELEASE_V1		      YES
RELEASE_V3		       RELEASE_V2		      YES

SQL>

If the editions have any associated editionable objects, then the CASCADE clause must be used to drop the editionable objects also.

Enabling Editions for a User

Enabling editions for a user is done using the ALTER USER command. This is not reversible. The result of this command can be seen by querying the EDITIONS_ENABLED column of the DBA_USERS view.

CONN / AS SYSDBA

CREATE USER edition_test IDENTIFIED BY edition_test DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER TO edition_test;

ALTER USER edition_test ENABLE EDITIONS;

SELECT editions_enabled
FROM   dba_users
WHERE  username = 'EDITION_TEST';

E
-
Y

SQL>

If the user contains existing non-editionable objects that depend on editionable objects, you must include the FORCE clause. This will leave the non-editionable objects in an invalid state.

Specific editions are granted to users with the USE ON EDITION clause.

GRANT USE ON EDITION release_v1 TO edition_test;
GRANT USE ON EDITION release_v2 TO edition_test;
GRANT USE ON EDITION release_v3 TO edition_test;

The edition is set at session level using the ALTER SESSION. The current edition for the session can be displayed using the SYS_CONTEXT function.

CONN edition_test/edition_test

ALTER SESSION SET EDITION = release_v1;

SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;

EDITION
--------------------------------------------------------------------------------
RELEASE_V1

SQL> 

-- Switch back to original edition.
ALTER SESSION SET EDITION = ora$base;

SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;

EDITION
--------------------------------------------------------------------------------
ORA$BASE

SQL>

When a new edition is used in a schema, all existing editionable object are inherited by the new edition from its parent. If any of those objects are changed using DML, then they are said to be actualized, along with their dependents, as a complete copy is made to the new edition.

Editionable Objects

The following objects are editionable:

FUNCTION
LIBRARY
PACKAGE and PACKAGE BODY
PROCEDURE
TRIGGER
TYPE and TYPE BODY
SYNONYM
VIEW

All other objects are noneditionable. Notice, that means tables can not be editioned!

Editioning Views

The presence of editions allows us to run multiple versions of the PL/SQL applications in a schema, but what happens if the code requires table changes, such as additional columns or renamed columns? This is where editioning views come into play. Editioning views are a wrapper over the base table that allow each edition to see the table in the form it needs. They can only be a straight query of the base table, but can display a subset of the columns and alias them to mimic a rename.

CREATE OR REPLACE EDITIONING VIEW view-name AS
SELECT col1,
       col3,
       col4 AS new_name
FROM   table-name;

By default editioning views are read-write, but they can be defined as read-only if a specific edition is kept for read-only use. Triggers should be applied directly to editioning views, rather than the base tables, which allows triggers to be edition-specific. The *_EDITIONING_VIEWS and *_EDITIONING_VIEWS_AE views display all editioning views and actualized editioning views respectively.

Oracle guarantee the execution plan for a query against an editioning view will be identical to the same query against the base table, so there is no performance penalty associated with the use of editioning views.

The documentation contains a full explanation of editioning views, but you can see how they can be used in the examples below.

Crossedition Triggers

The presence of an editioning view displaying a different representation of a base table may present a bit of a problem when DML is applied to it. A forward crossediton trigger takes data from the columns in the old edition and transforms it to the that required by the new edition, while reverse crossedition triggers do the opposite. This means that both editions can be used simultaneously without causing logical corruption of the data in the base table.

There is a lot of detail about crossedition triggers in the documention, but you can see how they can be used in Example 2 below.

Dictionary Views

The following views contain information relating to editions or editioning components:

The suffix "_AE" stands for "All Editions". So for example, the DBA_OBJECTS view only shows objects available under the current edition, but the DBA_OBJECTS_AE view shows all objects, regardless of the edition they belong to.

Preparing an Existing Application for Editioning

Preparing an existing application for editioning will require a little downtime, but all subsequent upgrades will be free of downtime. The steps involved in this preparation are:

Rename base tables. Doing this will invalidate all PL/SQL code with the exception of triggers.
Create editioning views over the base tables, matching the original table structures and names. PL/SQL objects will now recompile against editioning view.
Drop any triggers and compile them against editioning views, rather than base tables.
If necessary, apply VPD policies against editioning views, rather than base tables.
Revoke all privileges against tables and recreate them against the editioning views.

Example 1: No Data Transformation

In this example we add a new column to a base table, but want to run the existing application code against an editioning view with the new column omitted, and a new version of the application code against the amended table. This example assumes the EDITION_TEST user and the editions described earlier have already been created and the editions have been granted to the user.

First, we connect to the user, check the session edition and create the base table, editioning view and procedure that represents our application.

CONN edition_test/edition_test

ALTER SESSION SET EDITION = release_v1;

SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;

EDITION
--------------------------------------------------------------------------------
RELEASE_V1

SQL>

CREATE TABLE employees_tab (
  employee_id   NUMBER(5)    NOT NULL,
  name          VARCHAR2(40) NOT NULL,
  date_of_birth DATE         NOT NULL,
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

CREATE SEQUENCE employees_seq;

CREATE OR REPLACE EDITIONING VIEW employees AS
SELECT employee_id,
       name,
       date_of_birth
FROM   employees_tab;

CREATE OR REPLACE PROCEDURE create_employee (p_name          IN employees.name%TYPE,
                                             p_date_of_birth IN employees.date_of_birth%TYPE) AS
BEGIN
  INSERT INTO employees (employee_id, name, date_of_birth)
  VALUES (employees_seq.NEXTVAL, p_name, p_date_of_birth);
END create_employee;
/

Test the procedure to make sure it is working as expected.

BEGIN
  create_employee('Peter Parker', TO_DATE('01-JAN-2010', 'DD-MON-YYYY'));
  COMMIT;
END;
/

SELECT * FROM employees;

EMPLOYEE_ID NAME                                     DATE_OF_B
----------- ---------------------------------------- ---------
          2 Peter Parker                             01-JAN-10

SQL>

By checking the USER_OBJECTS_AE view we can see that the editionable objects (the procedure) are assigned to the session edition.

COLUMN object_name FORMAT A20

SELECT object_name, object_type, edition_name
FROM   user_objects_ae
ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- ------------------------------
CREATE_EMPLOYEE      PROCEDURE		 RELEASE_V1
EMPLOYEES	     VIEW		 RELEASE_V1
EMPLOYEES_PK	     INDEX
EMPLOYEES_SEQ	     SEQUENCE
EMPLOYEES_TAB	     TABLE

SQL>

Now switch to the new edition and run the procedure again, just to prove it has been inherited by the new edition.

ALTER SESSION SET EDITION = release_v2;

BEGIN
  create_employee('Clark Kent', TO_DATE('02-JAN-2010', 'DD-MON-YYYY'));
  COMMIT;
END;
/

SELECT * FROM employees;

EMPLOYEE_ID NAME                                     DATE_OF_B
----------- ---------------------------------------- ---------
          2 Peter Parker                             01-JAN-10
          3 Clark Kent                               02-JAN-10

SQL>

This represents our original application. Now we need to make the change to the base table.

ALTER TABLE employees_tab ADD (
  postcode   VARCHAR2(20)
);

The application code is unaffected as it accesses the table via the editioning view.

ALTER SESSION SET EDITION = release_v1;

BEGIN
  create_employee('Flash Gordon', TO_DATE('03-JAN-2010', 'DD-MON-YYYY'));
  COMMIT;
END;
/

SELECT * FROM employees;

EMPLOYEE_ID NAME                                     DATE_OF_B
----------- ---------------------------------------- ---------
          2 Peter Parker                             01-JAN-10
          3 Clark Kent                               02-JAN-10
          4 Flash Gordon                             03-JAN-10

SQL>

Note. If you ever need to manually recompile an object, make sure you use the REUSE SETTINGS clause.

Next we switch to the new edition and create an editioning view to reflect the new table structure.

ALTER SESSION SET EDITION = release_v2;

CREATE OR REPLACE EDITIONING VIEW employees AS
SELECT employee_id,
       name,
       date_of_birth,
       postcode
FROM   employees_tab;

We can now compile and test the new version of the procedure against the modified table (editioning view).

CREATE OR REPLACE PROCEDURE create_employee (p_name          IN employees.name%TYPE,
                                             p_date_of_birth IN employees.date_of_birth%TYPE,
                                             p_postcode      IN employees.postcode%TYPE) AS
BEGIN
  INSERT INTO employees (employee_id, name, date_of_birth, postcode)
  VALUES (employees_seq.NEXTVAL, p_name, p_date_of_birth, p_postcode);
END create_employee;
/

BEGIN
  create_employee('Mighty Mouse', TO_DATE('04-JAN-2010', 'DD-MON-YYYY'), 'AA1 2BB');
  COMMIT;
END;
/

SET LINESIZE 100
SELECT * FROM employees;

EMPLOYEE_ID NAME                                     DATE_OF_B POSTCODE
----------- ---------------------------------------- --------- --------------------
          2 Peter Parker                             01-JAN-10
          3 Clark Kent                               02-JAN-10
          4 Flash Gordon                             03-JAN-10
          5 Mighty Mouse                             04-JAN-10 AA1 2BB

SQL>

When we check the USER_OBJECTS_AE view we see the new editioning view and procedure are assigned to the new edition.

COLUMN object_name FORMAT A20

SELECT object_name, object_type, edition_name
FROM   user_objects_ae
ORDER BY object_name, edition_name;

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- ------------------------------
CREATE_EMPLOYEE      PROCEDURE		 RELEASE_V1
CREATE_EMPLOYEE      PROCEDURE		 RELEASE_V2
EMPLOYEES	     VIEW		 RELEASE_V1
EMPLOYEES	     VIEW		 RELEASE_V2
EMPLOYEES_PK	     INDEX
EMPLOYEES_SEQ	     SEQUENCE
EMPLOYEES_TAB	     TABLE

SQL>

So we have a single base table, being accessed by two different versions of the same application in a single schema. The version of the application used depends on the sessions edition setting.

Example 2: Two-Way Data Transformation

In the previous example we represented our employee name in a single column (NAME). In this example we will split that column into two columns (FIRST_NAME and LAST_NAME). We know how the three columns relate to each other so we can transform the data using crossedition triggers such that each edition populates the other editions view of the table correctly. First we alter the base table.

CONN edition_test/edition_test

ALTER TABLE employees_tab ADD (
  first_name VARCHAR2(20),
  last_name  VARCHAR2(20)
);

If we could populate the new columns and make them NOT NULL like this, but this is not the recommended method in the documentation.

UPDATE employees_tab
SET    first_name = SUBSTR(name, 1, INSTR(name, ' ')-1),
       last_name  = SUBSTR(name, INSTR(name, ' ')+1)
WHERE  first_name IS NULL;

ALTER TABLE employees_tab MODIFY (
  first_name VARCHAR2(20) NOT NULL,
  last_name  VARCHAR2(20) NOT NULL
);

Following the suggestion from the documentation, we amend the editioning view and procedure in the new edition to reflect the table changes.

ALTER SESSION SET EDITION = release_v3;

CREATE OR REPLACE EDITIONING VIEW employees AS
SELECT employee_id,
       first_name,
       last_name,
       date_of_birth,
       postcode
FROM   employees_tab;

CREATE OR REPLACE PROCEDURE create_employee (p_first_name    IN employees.first_name%TYPE,
                                             p_last_name     IN employees.last_name%TYPE,
                                             p_date_of_birth IN employees.date_of_birth%TYPE,
                                             p_postcode      IN employees.postcode%TYPE) AS
BEGIN
  INSERT INTO employees (employee_id, first_name, last_name, date_of_birth, postcode)
  VALUES (employees_seq.NEXTVAL, p_first_name, p_last_name, p_date_of_birth, p_postcode);
END create_employee;
/

Notice that neither the editioning view or procedure make any reference to the old NAME column. To make sure both the old and new columns stay in sync we need two cross edition triggers. The forward crossedition trigger takes data from the old edition and transforms it for use by the new edition. In this case that involves splitting the old NAME column into the new FIRST_NAME and LAST_NAME columns.

CREATE OR REPLACE TRIGGER employees_fwd_xed_trg
  BEFORE INSERT OR UPDATE ON employees_tab
  FOR EACH ROW
  FORWARD CROSSEDITION
  DISABLE
BEGIN
  :NEW.first_name := SUBSTR(:NEW.name, 1, INSTR(:NEW.name, ' ')-1);
  :NEW.last_name  := SUBSTR(:NEW.name, INSTR(:NEW.name, ' ')+1);
END employees_fwd_xed_trg;
/

The reverse crossedition trigger does the reverse by concatenating the new values together to represent the old column value.

CREATE OR REPLACE TRIGGER employees_rvrs_xed_trg
  BEFORE INSERT OR UPDATE ON employees_tab
  FOR EACH ROW
  REVERSE CROSSEDITION
  DISABLE
BEGIN
  :NEW.name := :NEW.first_name || ' ' || :NEW.last_name;
END employees_rvrs_xed_trg;
/

Once both triggers are in place we can enable them.

ALTER TRIGGER employees_fwd_xed_trg ENABLE;
ALTER TRIGGER employees_rvrs_xed_trg ENABLE;

Notice that both crossedition triggers were created in the child edition. If they are created in the parent edition the transformation will not work.

COLUMN object_name FORMAT A30

SELECT object_name, object_type, edition_name
FROM   user_objects_ae
ORDER BY object_name, edition_name;

OBJECT_NAME               OBJECT_TYPE         EDITION_NAME
------------------------- ------------------- ------------------------------
CREATE_EMPLOYEE 	       PROCEDURE	   RELEASE_V1
CREATE_EMPLOYEE 	       PROCEDURE	   RELEASE_V2
CREATE_EMPLOYEE 	       PROCEDURE	   RELEASE_V3
EMPLOYEES		       VIEW		   RELEASE_V1
EMPLOYEES		       VIEW		   RELEASE_V2
EMPLOYEES		       VIEW		   RELEASE_V3
EMPLOYEES_FWD_XED_TRG	       TRIGGER		   RELEASE_V3
EMPLOYEES_PK		       INDEX
EMPLOYEES_RVRS_XED_TRG	       TRIGGER		   RELEASE_V3
EMPLOYEES_SEQ		       SEQUENCE
EMPLOYEES_TAB		       TABLE

SQL>

At this point any new data applied to the tables will be populated properly, regardless of the edition that is used. Before we can proceed we need to make sure the old data is updated to reflect the change, then we can make the columns NOT NULL. We could use the method explained previously, or we can use the new cross edition trigger to do it for us. First we check that there are no outstanding DML operations on the base table.

DECLARE
  l_scn              NUMBER  := NULL;
  l_timeout CONSTANT INTEGER := NULL;
BEGIN
  IF NOT DBMS_UTILITY.wait_on_pending_dml(tables  => 'employees_tab',
                                          timeout => l_timeout,
                                          scn     => l_scn)
  THEN
    RAISE_APPLICATION_ERROR(-20000, 'Wait_On_Pending_DML() timed out. CETs were enabled before SCN: ' || l_scn);
  END IF;
END;
/

Then we use the DBMS_SQL package to update the base table, specifying the crossedition trigger to use to transform the data.

DECLARE
  l_cursor NUMBER := DBMS_SQL.open_cursor();
  l_return NUMBER;
BEGIN
  DBMS_SQL.PARSE(
    c                          => l_cursor,
    Language_Flag              => DBMS_SQL.NATIVE,
    Statement                  => 'UPDATE employees_tab SET name = name',
    apply_crossedition_trigger => 'employees_fwd_xed_trg'
  );
  l_return := DBMS_SQL.execute(l_cursor);
  DBMS_SQL.close_cursor(l_cursor);
  COMMIT;
END;
/

If the table were bigger we may want to parallelize the operation using the DBMS_PARALLEL_EXECUTE package, whose RUN_TASK procedure also supports the APPLY_CROSSEDITION_TRIGGER parameter.

Now the crossedition triggers are enabled and the data is up to date we can make the new columns NOT NULL.

ALTER TABLE employees_tab MODIFY (
  first_name VARCHAR2(20) NOT NULL,
  last_name  VARCHAR2(20) NOT NULL
);

Now to test the mechanism is working correctly. First we test the new edition.

SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;

EDITION
----------------------------------------------------------------------------------------------------
RELEASE_V3

SQL> 

BEGIN
  create_employee('Wonder', 'Woman', TO_DATE('01-JAN-2010', 'DD-MON-YYYY'), 'A11 2BB');
  COMMIT;
END;
/

-- Check the editioning view.
SET LINESIZE 100
SELECT * FROM employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME            DATE_OF_B POSTCODE
----------- -------------------- -------------------- --------- --------------------
          2 Peter                Parker               01-JAN-10
          3 Clark                Kent                 02-JAN-10
          4 Flash                Gordon               03-JAN-10
          5 Mighty               Mouse                04-JAN-10 AA1 2BB
          6 Wonder               Woman                01-JAN-10 A11 2BB

SQL>

-- Check the base table.
COLUMN name FORMAT A20
COLUMN postcode FORMAT A10

SELECT * FROM employees_tab;

EMPLOYEE_ID NAME            DATE_OF_B POSTCODE   FIRST_NAME           LAST_NAME
----------- --------------- --------- ---------- -------------------- --------------------
          2 Peter Parker    01-JAN-10            Peter                Parker
          3 Clark Kent      02-JAN-10            Clark                Kent
          4 Flash Gordon    03-JAN-10            Flash                Gordon
          5 Mighty Mouse    04-JAN-10 AA1 2BB    Mighty               Mouse
          6 Wonder Woman    01-JAN-10 A11 2BB    Wonder               Woman

SQL>

Next we switch to the old edition and test that.

ALTER SESSION SET EDITION = release_v2;

BEGIN
  create_employee('Inspector Gadget', TO_DATE('01-JAN-2010', 'DD-MON-YYYY'), 'A12 2BB');
  COMMIT;
END;
/

-- Check editioning view.
COLUMN name FORMAT A25

SELECT * FROM employees;

EMPLOYEE_ID NAME		      DATE_OF_B POSTCODE
----------- ------------------------- --------- ----------
	  2 Peter Parker	      01-JAN-10
	  3 Clark Kent		      02-JAN-10
	  4 Flash Gordon	      03-JAN-10
	  5 Mighty Mouse	      04-JAN-10 AA1 2BB
	  6 Wonder Woman	      01-JAN-10 A11 2BB
	  7 Inspector Gadget	      01-JAN-10 A12 2BB

SQL>

-- Check base table.
SELECT * FROM employees_tab;

EMPLOYEE_ID NAME                      DATE_OF_B POSTCODE   FIRST_NAME           LAST_NAME
----------- ------------------------- --------- ---------- -------------------- --------------------
          2 Peter Parker              01-JAN-10            Peter                Parker
          3 Clark Kent                02-JAN-10            Clark                Kent
          4 Flash Gordon              03-JAN-10            Flash                Gordon
          5 Mighty Mouse              04-JAN-10 AA1 2BB    Mighty               Mouse
          6 Wonder Woman              01-JAN-10 A11 2BB    Wonder               Woman
          7 Inspector Gadget          01-JAN-10 A12 2BB    Inspector            Gadget

SQL>

So we have both applications running as required and the crossedition triggers are making sure the data is transformed correctly.

Switching Between Editions

There are several ways to connect to specific editions, or switch between editions. We've already see the ALTER SESSION command.

ALTER SESSION SET EDITION = ora$base;

The ORA_EDITION environment variable determines which edition SQL*Plus will connect to.

# UNIX/Linux
export ORA_EDITION=ora\$base

Rem Windows
set ORA_EDITION=ora$base

If the ORA_EDITION variable is not set, or you want to override the setting, you can specify the edition you want by adding the EDITION parameter to the SQL*Plus command.

$ sqlplus username/password@service edition=ora\$base

From within SQL*Plus you can add the EDITION parameter to the CONNECT command.

SQL> CONNECT username/password@service EDITION=ora$base

Services and Editions

In 11.2.0.2 the CREATE_SERVICE and MODIFY_SERVICE procedures in the DBMS_SERVICE package have been modified to include an EDITION parameter allowing an edition to be linked to a service.

Associating an edition with a service means you no longer need to specify an edition when connecting to the database or switch the edition after connecting. As a result, you can ignore the methods shown in the Switching Between Editions section.

To test this funcitonality, check what services are available.

CONN / AS SYSDBA

COLUMN name FORMAT A20

SELECT name, edition
FROM   dba_services;

NAME                 EDITION
-------------------- ------------------------------
SYS$BACKGROUND
SYS$USERS
DB11GXDB
DB11G.WORLD

4 rows selected.

SQL>

Test what edition our test user defaults to when we connect using the "DB11G.WORLD" service.

CONN edition_test/edition_test@//localhost:1521/DB11G.WORLD
SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;

EDITION
----------------------------------------------------------------------------------------------------
ORA$BASE

1 row selected.

SQL>

Alter the edition associated with the "DB11G.WORLD" service.

CONN / AS SYSDBA

BEGIN
  DBMS_SERVICE.modify_service(
    service_name   => 'DB11G.WORLD',
    edition        => 'RELEASE_V1',
    modify_edition => TRUE);
END;
/

COLUMN name FORMAT A20

SELECT name, edition
FROM   dba_services;

NAME                 EDITION
-------------------- ------------------------------
SYS$BACKGROUND
SYS$USERS
DB11GXDB
DB11G.WORLD          RELEASE_V1

4 rows selected.

SQL>

Show the edition is being used correctly when we connect using the "DB11G.WORLD" service.

CONN edition_test/edition_test@//localhost:1521/DB11G.WORLD
SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;

EDITION
----------------------------------------------------------------------------------------------------
RELEASE_V1

1 row selected.

SQL>

Remember, for this to work the user must have access to the edition. If we connect to any user without the correct setup we get an error.

CONN test/test@//localhost:1521/DB11G.WORLD
ERROR:
ORA-38802: edition does not exist


Warning: You are no longer connected to ORACLE.
SQL>

To revert the service to using the default edition, simply set the edition to NULL.

CONN / AS SYSDBA

BEGIN
  DBMS_SERVICE.modify_service(
    service_name   => 'DB11G.WORLD',
    edition        => NULL,
    modify_edition => TRUE);
END;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.