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
This helped me a lot understand this new feature.
Thanks