EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned

This is part 6 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so changes can be applied in both online and safe way. I discussed enabling, creating and using editions; session edition; and editioned objects.
In part 4 we discussed another challenge of online upgrades – invalidation of dependent objects.
In part 5 we saw the importance of explicit actualization of dependent objects.
In this part we’ll dive into the differences between editioned and non-editioned objects, editionable and non-editionable object types, and the relationships between them.

Visit the index page for all the parts of the series

In a previous post, we enabled the user DEMO_EBR for editions. As a result, some of the existing objects of DEMO_EBR (the package specs and package bodies) have become editioned, and the others (a table and an index) have not. Why is that?
Before I answer this question, let’s see what an editioned object is exactly, and what a non-editioned object is.

Editioned and Non-Editioned Objects

An editioned object is an object that may have multiple instances within the same schema, in different editions. In each edition we see one specific instance of the editioned object: if the object is actual in the current edition, then we see it, and if it is inherited in the current edition, then we see the “closest” object instance that is actual amongst the ancestor editions. Editioned objects are the fundamental tool that enables online application upgrades – while the users are using one instance of an object, the developers can make changes to another instance of this object which is still not exposed to the users.
A non-editioned object is an object that has only one instance in the schema, and this single object instance is shared by all the editions.

This means there is one huge (with respect to online upgrades) difference between editioned and non-editioned objects. Every instance of an editioned object is invisible to the ancestor editions, so we can make changes to such objects in the privacy of a new and unexposed edition. But changes to non-editioned objects are immediately exposed (and potentially impact) all the editions, including the ones that are currently used by production users.

And this leads to the following important rule:

Non-editioned objects cannot depend on editioned objects

(there are a couple of exceptions to this rule, but I’ll discuss them in another post).

How can we tell if an object is editioned or not? By looking at the EDITION_NAME column of the DBA_OBJECTS (or DBA_OBJECTS_AE) view. For non-editioned objects this column is null. For editioned objects this column contains the edition in which the object is actual.

Let’s see for example all the objects in all the editions in the DEMO_EBR schema at this point (after completing two upgrades already – in part 3 and part 5):

ORA$BASE> break on object_name dup on object_type dup skip 1

ORA$BASE> select object_type,object_name,edition_name
  2  from  dba_objects_ae
  3  where  owner = 'DEMO_EBR'
  4  order  by nvl2(edition_name,2,1),object_type,object_name,last_ddl_time;

OBJECT_TYPE  OBJECT_NAME  EDITION_NAME
------------ ------------ ------------
INDEX        PEOPLE_PK

TABLE        PEOPLE

PACKAGE      APP_MGR      ORA$BASE

PACKAGE      PEOPLE_DL    ORA$BASE
PACKAGE      PEOPLE_DL    V2

PACKAGE BODY APP_MGR      ORA$BASE
PACKAGE BODY APP_MGR      V2

PACKAGE BODY PEOPLE_DL    ORA$BASE
PACKAGE BODY PEOPLE_DL    V1
PACKAGE BODY PEOPLE_DL    V2


10 rows selected.

We can see that the PEOPLE table and the PEOPLE_PK index are non-editioned objects, and that the package specs and package bodies are editioned objects. The APP_MGR package spec, for example, has one instance (actual in ORA$BASE and inherited in V1 and V2), and the APP_MGR package body has two instances (one that is actual in ORA$BASE and inherited in V1, and one that is actual in V2).

So, why are some of the objects editioned and the others are non-editioned?

The general high-level answer is that code objects (like packages) can be editioned, and data objects (like tables) cannot.
This makes sense, as it will not be practical to duplicate tons of data every time we upgrade the application.
However, we’ll see in later posts that we have proper ways to handle non-editioned data objects as if they were editioned.
And regarding code objects – as of Oracle 12c we have the flexibility to control which objects are editioned and which are not. This is the focus of the next sections.

Database-Level Editionable Object Types

The superset of object types that are editionable is determined in the database level. As of Oracle 12.1 we can see the database-level editionable object types in the V$EDITIONABLE_TYPES view:

ORA$BASE> select editionable_type
  2  from v$editionable_types
  3  order by editionable_type;

EDITIONABLE_TYPE
----------------------------
FUNCTION
LIBRARY
PACKAGE
PACKAGE BODY
PROCEDURE
SQL TRANSLATION PROFILE
SYNONYM
TRIGGER
TYPE
TYPE BODY
VIEW

11 rows selected.

In Oracle 11.2 the list is the same, except for SQL TRANSLATION PROFILE (this object type was introduced in 12.1).
In addition, regarding the SYNONYM object type, in 11.2 only private synonyms are editionable, while as of 12.1 both private and public synonyms are editionable.

“Non-Enabled For Editions” Users

If a user is not enabled for editions, then all its objects are non-editioned, regardless of their types.

Schema-Level Editionable Object Types

In Oracle 11.2, when enabling a user for editions, then all the database-level editionable types become editionable for the user.
In Oracle 12c, this behavior is still the default one, maintaining backward compatibility with 11.2. And we can see which types are editionable in the schema level using the DBA_EDITIONED_TYPES view (introduced in 12.1):

ORA$BASE> create user u1 identified by u1;

User created.

ORA$BASE> select object_type
  2  from dba_editioned_types
  3  where schema='U1'
  4  order by object_type;

no rows selected

ORA$BASE> alter user u1 enable editions;

User altered.

ORA$BASE> select object_type
  2  from dba_editioned_types
  3  where schema='U1'
  4  order by object_type;

OBJECT_TYPE
------------
ASSEMBLY
FUNCTION
LIBRARY
PACKAGE
PACKAGE BODY
PROCEDURE
SYNONYM
TRIGGER
TYPE
TYPE BODY
VIEW

11 rows selected.

Note: the documentation of ALTER USER says “If you omit the FOR clause, then the user can create editionable objects for all editionable object types”, but we can see that “SQL TRANSLATION PROFILE” does not appear in the last query’s result. I guess a more accurate phrasing would be “If you omit the FOR clause, then the user can create editionable objects for all the object types that are editionable in Oracle 11.2”. However, when enabling a user for editions as part of the CREATE USER statement, then all the database-level editionable types (including SQL TRANSLATION PROFILE) become editionable for the user.

As I wrote above, this is the default behavior as of 12.1. The non-default syntax – using the FOR clause – allows us to selectively specify which types will become editionable. For example, we can now add SQL TRANSLATION PROFILE to the editionable types of U1:

ORA$BASE> alter user u1 enable editions for sql translation profile;

User altered.

ORA$BASE> select object_type
  2  from dba_editioned_types
  3  where schema='U1'
  4  order by object_type;

OBJECT_TYPE
------------------------------
ASSEMBLY
FUNCTION
LIBRARY
PACKAGE
PACKAGE BODY
PROCEDURE
SQL TRANSLATION PROFILE
SYNONYM
TRIGGER
TYPE
TYPE BODY
VIEW

12 rows selected.

As another example, let’s create a user and enable it for editions only for packages, functions and procedures:

ORA$BASE> create user u2 identified by u2;

User created.

ORA$BASE> alter user u2 enable editions for package,function,procedure;

User altered.

ORA$BASE> select object_type
  2  from dba_editioned_types
  3  where schema='U2'
  4  order by object_type;

OBJECT_TYPE
------------------------------
FUNCTION
PACKAGE
PACKAGE BODY
PROCEDURE

Note that making PACKAGE editionable, implicitly makes PACKAGE BODY editionable as well. We cannot even specify PACKAGE BDOY explicitly in the FOR clause. The same is true for TYPE and TYPE BODY.

Note: after enabling a user for editions without specifying the FOR clause, we can see in DBA_EDITIONED_TYPES the ASSEMBLY object type. This type does not appear in V$EDITIONABLE_TYPES, and we cannot specify it explicitly using “ALTER USER … ENABLE EDITIONS FOR ASSEMBLY”. I have no idea what it means…

Editionable and Non-Editionable Objects

If we create an object of a type that is database-level non-editionable (and therefore also schema-level non-editionable), then the created object is obviously non-editioned, and will never become editioned.

If we create an object of a type that is database-level editionable but schema-level non-editionable, then the created object is non-editioned, but may have the potential to become editioned in the future. This can happen if later on the object type becomes editionable in the schema level (using ALTER USER user ENABLE EDITIONS [FOR type]). An object that has this potential is considered an editionable object; otherwise it is considered a non-editionable object. This attribute can be defined when creating or altering the object, and by default such objects are editionable.

Note: the ability to define specific objects as editionable or non-editionable was introduced in Oracle 12.1. In 11.2 all the objects of editionable types are editionable.

Let’s see an example. We start by creating a user that is not enabled for editions:

ORA$BASE> grant connect,resource to u3 identified by u3;

Grant succeeded.

Let’s create a few objects in U3 – a table, a sequence and an index (via a single CREATE TABLE statement) – all these types are non-editionable:

ORA$BASE> conn u3/u3
Connected. 
ORA$BASE> create table t (
  2    id integer generated as identity primary key,
  3    data varchar2(100)
  4  );

Table created.

And now we’ll create two procedures – p1 as editionable (implicitly) and p2 as non-editionable (explicitly). Remember that PROCEDURE is an editionable type in the database level, but it is (still) a non-editionable type in this schema (as U3 has not been enabled for editions).

ORA$BASE> create procedure p1 as begin null; end;
  2  /

Procedure created.

ORA$BASE> create noneditionable procedure p2 as begin null; end;
  2  /

Procedure created. 

We can see in USER_OBJECTS that all the objects are non-editioned (the EDITION_NAME column is null for all the objects). The EDITIONABLE column is null for the table, sequence and index, as these objects have no potential to become editioned. And we can see from the EDITIONABLE column that the P1 procedure is editionable while the P2 procedure is non-editionable.

ORA$BASE> select object_type,object_name,edition_name,editionable
  2  from user_objects;

OBJECT_TYPE  OBJECT_NAME          EDITION_NAME EDITIONABLE
------------ -------------------- ------------ ------------
TABLE        T
INDEX        SYS_C0055749
SEQUENCE     ISEQ$$_125378
PROCEDURE    P1                                Y
PROCEDURE    P2                                N

Now let’s enable the user for editions, and repeat the query from USER_OBJECTS:

ORA$BASE> conn system
Enter password:
Connected.
ORA$BASE> alter user u3 enable editions;

User altered.

ORA$BASE> conn u3/u3
Connected.
ORA$BASE> select object_type,object_name,edition_name, editionable
  2  from user_objects;

OBJECT_TYPE  OBJECT_NAME          EDITION_NAME EDITIONABLE
------------ -------------------- ------------ ------------
TABLE        T
INDEX        SYS_C0055749
SEQUENCE     ISEQ$$_125378
PROCEDURE    P1                   ORA$BASE     Y
PROCEDURE    P2                                N

And we can see that P1 has become editioned (in the ORA$BASE edition), while P2 remained non-editioned.

If we create an object of a type that is schema-level editionable, then by default the new object is editioned (and in 11.2 this is the only option). As of 12.1 we can choose to create such an object as non-editioned, by specifying the noneditionable keyword.
Let’s continue the previous example – the U3 user is already enabled for editions at this point, and we’ll create 2 new procedures: P3 as editioned (the default) and P4 as non-editioned.

ORA$BASE> create procedure p3 as begin null; end;
  2  /

Procedure created.

ORA$BASE> create noneditionable procedure p4 as begin null; end;
  2  /

Procedure created.

ORA$BASE> select object_type,object_name,edition_name,editionable
  2  from user_objects;

OBJECT_TYPE  OBJECT_NAME          EDITION_NAME EDITIONABLE
------------ -------------------- ------------ ------------
TABLE        T
INDEX        SYS_C0055749
SEQUENCE     ISEQ$$_125378
PROCEDURE    P1                   ORA$BASE     Y
PROCEDURE    P2                                N
PROCEDURE    P3                   ORA$BASE     Y
PROCEDURE    P4                                N

7 rows selected.

Ok, But Why Do We Need This?

A legitimate question at this point would be: “Why do we need the ability to define some of the code objects as non-editioned?”
The major reason is the rule that non-editioned objects cannot depend on editioned objects. Think, for example, of a table that one of its column is based on a user-defined type. As the table is a non-editioned object, then this user-defined type must be non-editioned as well.
I’ll discuss this in detail in a future post.

For other parts of the EBR series, please visit the index page

Leave a Reply

Your email address will not be published.