You have your editions, and you can tell which edition each session is currently using, now how do you control that?

First we’ll look at sessions that you control. If you, or code under your control, initiates the edition, you can control the edition with an ALTER SESSION command, like this:

alter session set edition=MYEDITION;

There are shortcuts to this. When using sqlplus, you can set the ORA_EDITION env var or pass the edition on the command line like this:

sqlplus / as sysdba edition=MYEDITION

Note that although the SYS user cannot have editions enabled, it is still true that every login session has a current edition, since it may access objects owned by users that do have editions enabled.

Some other tools may allow you to set an edition for a database connection. This is really all just making the alter sessions command automatic.

You can change the edition in a session as often as you wish, but you cannot be in a place where changing the edition would make the current context invalid. For example, you may not change the edition inside PL/SQL because then you might be executing code from the old edition in the context of the new edition. I know you were thinking of login triggers to change the edition, but you can forget that.

So what about sessions that you don’t control in any way? If they are already running, you cannot change their edition. Think about the havoc that would wreck if it was allowed!

You can however influence the edition that they connect with. I say influence because the session can override your setting with an alter session of their own. The main way you do this is by changing the default edition for the entire database. Think of the default edition as the stable, steady state between code deploys. With no other intervention, all sessions connect as this edition and everything is consistent.

What if you want only certain sessions to connect with the newly deployed code in a non-default edition? The ideal way is to have those sessions make the alter session command, but if you don’t want to do that you have one final option, cluster-ready services.

The -t option to the srvctl modify service command will allow you to specify the edition for that service. Be careful in implementing this, it doesn’t always work reliably, but it’s the best option for controlling the connections of general apps. For one thing, please resist the temptation to use the dbms_services package to change the edition. It has all the options needed and will change the edition in the DBA_SERVICES view, but it will not change it in CRS correctly. Another bug is that if you try to unset the edition with the srvctl command (with -r “”), that will remove it from CRS (as shown by srvctl config), but leave it in the data dictionary, so then you must use the services package as a second step to truly clear it back to use the default edition.

My recommendation is that you not alter the edition of a service to effect this change, but create separate services for each edition and change the service name in the connect details the session will use. Editing the tnsnames.ora on the webserver for example.

So now you have 2 editions that have identical objects, one of them is set as the database default edition. How can you tell which edition a session is using?

If you are controlling the session and can run SQL, just run this:

SELECT sys_context(‘USERENV’, ‘CURRENT_EDITION_NAME’) edition FROM dual;

If you want to see what the current edition of other running sessions, try this:

SELECT o.object_name edition, s.*
FROM gv$session s
LEFT OUTER JOIN dba_objects o ON (s.session_edition_id = o.object_id);

Want to know the what the default edition is set to? Run this:

SELECT property_value FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’;

Combining those, if you want to find all the sessions that are using a non-default edition, this will give you that information:

SELECT o.object_name edition, s.*
FROM gv$session s
LEFT OUTER JOIN dba_objects o ON (s.session_edition_id = o.object_id)
WHERE o.object_name NOT IN (SELECT property_value FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’);

So now you know what edition a session is running, next time we’ll talk about how to change the edition and control how other sessions connect.

I promised in the last post I would tell you how to keep stable editions without bloating the data dictionary. As you probably surmised from the title, the answer is to make only 2 editions and “flip flop” between them. They must, by Oracle rules, be parent and child, but you will treat them as sibling editions, always performing the same changes to both (but not at the same time).

Pick names that don’t imply a parent-child hierarchy or sequence, 2 unordered equals. Colors or animal names for example. You will explicitly avoid any inheritance between these editions. Instantiate all objects in the child edition, including synonyms. Even make explicit grants to the objects in the child edition. “Shared nothing” is the goal.

There is one flaw in this, if you deploy new code to the parent edition first, any new editioned objects will appear in the child edition before you deploy code explicitly in that edition. In most cases, the new objects will be ignored by the old code, but there is one type of editioned object that executes without being called — triggers. If the new trigger is valid in the child edition, it will execute, which may be ok. But, if the new trigger is not valid, DML that calls that trigger will begin to fail. It is best to assume that you need to avoid the new trigger being visible in the child edition, and there are 2 ways to do that:

1) Always do the first deploy to the child edition – then, when you are running in that edition and do the “catch up” deploy to the parent, the triggers already exist in the child edition and so the trigger in the parent edition is no longer new.

2) Pre-create a “dummy” trigger in the child edition – Make a trigger with the same name, but a body of “null;”, and install it into the child edition, before you start the real deploy. You can even drop that trigger immediately, once a object has been created in an edition an object with that name will always exist. Even if you drop the trigger, the parent trigger of the same name will never bleed through. If you don’t drop it, make sure the real trigger in the real deploy is installed with “create or replace” to overwrite the dummy trigger.

Personally, I recommend the second option as the one that can never fail due to confusion about the edition you are deploying to. It can be done far in advance if desired to ensure that it is done. If you can setup a process that allows you to switch all connections to the parent easily, by all means do that. Nothing says you can’t do both.

According to the examples in the Oracle documentation, and the various web pages that describe how to implement editioning, you should create a new child edition for every code deploy. I can tell you that does not scale well at all. If you do frequent deploys, as most companies do, you can end up with dozens, perhaps hundreds of editions. We began to have major instability after hitting about 10 editions. I believe it was inter-edition dependencies that caused this, and so I began to instantiate every object in a child edition before switching to that as the default edition. Once I did that, the database regained the code stability we had before using editions. One problem solved!

The way to instantiate the objects into a child edition is to compile all the objects that are being referenced from earlier editions. If you switch into the child edition and run this SQL, it will generate all the DDL needed to make the edition stable.

SELECT decode(object_type,’PACKAGE BODY’,’alter package ‘||owner||’.’||object_name||’ compile body;’,
‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile;’)||’  –‘||edition_name cmd
FROM dba_objects o WHERE edition_name <> sys_context(‘USERENV’, ‘CURRENT_EDITION_NAME’);

The only problem with this approach is that you just doubled the number of editioned objects in your database, by adding a single child edition. That also will not scale well. It’s like adding new shingles on top of the old ones on your roof. You can do that once or twice, but if you do it a hundred times . . .

The solution will be in my next post . . .

I was going to explain about the edition object in this post, but in looking at the existing views around this object, dba_editions and dba_edition_comments, it seemed like they were missing some crucial info also. The 3rd new view I call dba_edition_info, it adds the columns of these 2 views together and includes a column that shows the create order of the editions. I used to generate an ordered list of the editions with SQL like this:

SELECT edition_name FROM dba_editions START WITH edition_name = ‘ORA$BASE’ CONNECT BY PRIOR edition_name = parent_edition_name ORDER BY LEVEL;

That works, but it adds a lot of complexity since we can also derive this from the base data dictionary table. It’s possible my new view will stop working correctly if Oracle adds new capabilities, such as the ability to have more than one child edition linked to a single parent, but for now the editions present as a single chain, a linked list represented in a table. This restriction of a single child for any parent presents some unique challenges for the process I use for deploying new code, and I hope that it is lifted at some point in the future, but until then I’m confident in recommending this view as a better way to get all the information about the edition objects in the database.

The query above can be re-written much more simply with the new view:

SELECT edition_name FROM dba_edition_info ORDER BY create_order;

Here is the source code to create the dba_edition_info view. Run this as sys, then you can query the resulting view as you would any other sys view.

create or replace view sys.dba_edition_info (edition_name, create_order, parent_edition_name, usable, comments)
as
select o.name, utl_raw.length(e.code), po.name, decode(bitand(e.flags,1),1,’NO’,’YES’), c.comment$
from sys.obj$ o, sys.edition$ e, sys.obj$ po, sys.com$ c
where o.obj# = e.obj# and po.obj# (+)= e.p_obj# and o.obj# = c.obj# (+);
comment on table SYS.DBA_EDITION_INFO is ‘Describes all editions in the database’;
comment on column SYS.DBA_EDITION_INFO.EDITION_NAME is ‘Name of the edition’;
comment on column SYS.DBA_EDITION_INFO.CREATE_ORDER is ‘Edition order’;
comment on column SYS.DBA_EDITION_INFO.PARENT_EDITION_NAME is ‘Name of the parent edition for this edition’;
comment on column SYS.DBA_EDITION_INFO.USABLE is ‘A value of ”YES” means edition is usable and ”NO” means unusable’;
comment on column SYS.DBA_EDITION_INFO.COMMENTS is ‘Edition comment’;
create or replace public synonym dba_edition_info for sys.dba_edition_info;

I bet you’re really wanting to hear about the other missing view — well here we are with part 2. The SYS.DBA_TAB_PRIVS_AE view seems a bit odd since it’s well documented that grants are not editioned in the database. That is true in a strict sense that a grant is not an object (versus a synonym, which is an object). A grant is more like a property of an object, if you delete the object, the grant also disappears (again, unlike a synonym). So though a grant does not have an edition associated with it, it does belong to an object and that object can be editioned.

If an object is newly created, it has no object grants. But if you do either an “alter compile” or “replace” (as in create or replace), then the grants are left from before. This applies across editions as well. If you instantiate an object in a child edition (with either compile or replace), it inherits all grants from the parent edition’s object. However, it does not maintain that relationship for future grants. After you instantiate an object in a child edition, it is de-coupled from the parent object and you need to maintain consistency of the grants between the editions (if that is important to you). I do this with a query on the SYS.DBA_TAB_PRIVS_AE_DH, the new DD view I will show you how to create, but first the SQL to fix any missing grants. This accepts 2 edition names and compares the grants for the objects in those 2 editions.

ACCEPT Edition1 CHAR PROMPT ‘First Edition Name: ‘
ACCEPT Edition2 CHAR PROMPT ‘Second Edition Name: ‘
PROMPT alter system set edition=&Edition1;;
SELECT ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’;’ FROM sys.dba_tab_privs_ae_dh WHERE edition_name = UPPER(‘&Edition2’)
MINUS
SELECT ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’;’ FROM sys.dba_tab_privs_ae_dh WHERE edition_name = UPPER(‘&Edition1’);
PROMPT
PROMPT alter system set edition=&Edition2;;
SELECT ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’;’ FROM sys.dba_tab_privs_ae_dh WHERE edition_name = UPPER(‘&Edition1’)
MINUS
SELECT ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’;’ FROM sys.dba_tab_privs_ae_dh WHERE edition_name = UPPER(‘&Edition2’);

Of course to run this, you will need to first create the sys.dba_tab_privs_ae_dh view like this:

create or replace view sys.dba_tab_privs_ae_dh
(grantee, owner, table_name, grantor, privilege, grantable, hierarchy, edition_name)
as
select ue.name, u.name, o.name, ur.name, tpm.name,
decode(mod(oa.option$,2), 1, ‘YES’, ‘NO’),
decode(bitand(oa.option$,2), 2, ‘YES’, ‘NO’), o.defining_edition
from sys.objauth$ oa, sys.”_ACTUAL_EDITION_OBJ” o, sys.user$ u, sys.user$ ur,
sys.user$ ue, table_privilege_map tpm
where oa.obj# = o.obj#
and oa.grantor# = ur.user#
and oa.grantee# = ue.user#
and oa.col# is null
and oa.privilege# = tpm.privilege
and u.user# = o.owner#;
comment on table SYS.dba_tab_privs_ae_dh is ‘All grants on objects in the database with edition’;
comment on column SYS.dba_tab_privs_ae_dh.GRANTEE is ‘User to whom access was granted’;
comment on column SYS.dba_tab_privs_ae_dh.OWNER is ‘Owner of the object’;
comment on column SYS.dba_tab_privs_ae_dh.TABLE_NAME is ‘Name of the object’;
comment on column SYS.dba_tab_privs_ae_dh.GRANTOR is ‘Name of the user who performed the grant’;
comment on column SYS.dba_tab_privs_ae_dh.PRIVILEGE is ‘Table Privilege’;
comment on column SYS.dba_tab_privs_ae_dh.GRANTABLE is ‘Privilege is grantable’;
comment on column SYS.dba_tab_privs_ae_dh.HIERARCHY is ‘Privilege is with hierarchy option’;
comment on column SYS.dba_tab_privs_ae_dh.edition_name  is ‘Name of the edition in which the object is actual’;
create or replace public synonym dba_tab_privs_ae_dh for sys.dba_tab_privs_ae_dh;

In an earlier post, I mentioned the new data dictionary views that support editioning.  Here is a list of the DBA views (they also exist as USER and ALL, as most other DD views):

SYS.DBA_VIEWS_AE
SYS.DBA_SOURCE_AE
SYS.DBA_OBJECTS_AE
SYS.DBA_ERRORS_AE
SYS.DBA_EDITIONING_VIEW_COLS_AE
SYS.DBA_EDITIONING_VIEWS_AE

See anything missing? Most of the other views you might want are easily derivable by joining the traditional view (say dba_triggers) to the dba_objects_ae view. It still might be useful to have something like a dba_triggers_ae view, but I haven’t found it hard to work around that.Usually, I switch my session to the edition I want to work in if I really need to see something like the state of the triggers.

There are 2 views that I really wish were available, dba_dependencies_ae and dba_tab_privs_ae. I missed them so much that I ended up writing my own DD views for these. Switching between editions just doesn’t give me the ability to compare the way that I want to.

To create these new views, I compared the source for dba_objects with dba_objects_ae to see that changes would be needed, then created a new view to serve the purpose of dba_dependencies_ae. Do not use this name because it is very likely that a future upgrade from Oracle will recognize this shortcoming and create the view. Having a view with the same name already present could cause the upgrade to fail, so it’s better to make a name they won’t likely use, let’s say dba_dependencies_ae_dh.

Once you have this view, you can query for cross-edition dependencies with something like this:  SELECT * FROM sys.dba_dependencies_ae_dh WHERE edition_name <> referenced_edition_name;
Cross-edition dependencies are a “feature” of editioning that seems to have been a root cause of much of the instability that I experienced in the early days of implementing editioning. If you think about this a bit you can probably envision scenarios where the dependencies can get a bit confusing. My policy has become to never allow cross-edition dependencies, but to fully instantiate all the objects in each edition. That has the downside of increasing the object counts in your data dictionary, but I will cover how I deal with that in some future post.

Now for the source to create the view. Just copy all this and run it as SYS in your db.

create or replace view dba_dependencies_ae_dh
(owner, name, type, edition_name, object_id, referenced_owner, referenced_name, referenced_type, referenced_edition_name, referenced_object_id, referenced_link_name, dependency_type)
as
select u.name, o.name,
decode(o.type#, 0, ‘NEXT OBJECT’, 1, ‘INDEX’, 2, ‘TABLE’, 3, ‘CLUSTER’,
4, ‘VIEW’, 5, ‘SYNONYM’, 6, ‘SEQUENCE’, 7, ‘PROCEDURE’,
8, ‘FUNCTION’, 9, ‘PACKAGE’, 10, ‘NON-EXISTENT’,
11, ‘PACKAGE BODY’, 12, ‘TRIGGER’,
13, ‘TYPE’, 14, ‘TYPE BODY’, 22, ‘LIBRARY’,
28, ‘JAVA SOURCE’, 29, ‘JAVA CLASS’,
32, ‘INDEXTYPE’, 33, ‘OPERATOR’,
42, ‘MATERIALIZED VIEW’, 43, ‘DIMENSION’,
46, ‘RULE SET’, 55, ‘XML SCHEMA’, 56, ‘JAVA DATA’,
59, ‘RULE’, 62, ‘EVALUATION CONTXT’, 87, ‘ASSEMBLY’,
92, ‘CUBE DIMENSION’, 93, ‘CUBE’,
94, ‘MEASURE FOLDER’, 95, ‘CUBE BUILD PROCESS’,
‘UNDEFINED’),
o.defining_edition,
o.obj#,
decode(po.linkname, null, pu.name, po.remoteowner), po.name,
decode(po.type#, 0, ‘NEXT OBJECT’, 1, ‘INDEX’, 2, ‘TABLE’, 3, ‘CLUSTER’,
4, ‘VIEW’, 5, ‘SYNONYM’, 6, ‘SEQUENCE’, 7, ‘PROCEDURE’,
8, ‘FUNCTION’, 9, ‘PACKAGE’, 10, ‘NON-EXISTENT’,
11, ‘PACKAGE BODY’, 12, ‘TRIGGER’,
13, ‘TYPE’, 14, ‘TYPE BODY’, 22, ‘LIBRARY’,
28, ‘JAVA SOURCE’, 29, ‘JAVA CLASS’,
32, ‘INDEXTYPE’, 33, ‘OPERATOR’,
42, ‘MATERIALIZED VIEW’, 43, ‘DIMENSION’,
46, ‘RULE SET’, 55, ‘XML SCHEMA’, 56, ‘JAVA DATA’,
59, ‘RULE’, 62, ‘EVALUATION CONTXT’, 87, ‘ASSEMBLY’,
92, ‘CUBE DIMENSION’, 93, ‘CUBE’,
94, ‘MEASURE FOLDER’, 95, ‘CUBE BUILD PROCESS’,
‘UNDEFINED’),
po.editionname,
po.obj#,
po.linkname,
decode(bitand(d.property, 3), 2, ‘REF’, ‘HARD’)
from sys.”_ACTUAL_EDITION_OBJ” o, sys.dependency$ d, sys.user$ u, sys.user$ pu,
(select co.obj#, co.owner#, co.name, co.type#, co.remoteowner, co.linkname, co.defining_edition editionname from sys.”_ACTUAL_EDITION_OBJ” co
union all
select kobjn_kqfp, 0, name_kqfp, decode(type_kqfp, 1, 9, 2, 11, 3, 10, 0), NULL, NULL, NULL from sys.x$kqfp
union all
select kqftaobj, 0, kqftanam, 2, NULL, NULL, NULL from sys.x$kqfta
union all
select kqfviobj, 0, kqfvinam, 4, NULL, NULL, NULL from sys.x$kqfvi) po
where o.obj# = d.d_obj#
and o.owner# = u.user#
and po.obj# = d.p_obj#
and po.owner# = pu.user#;
comment on column dba_dependencies_ae_dh.OWNER is ‘Owner of the object’;
comment on column dba_dependencies_ae_dh.NAME is ‘Name of the object’;
comment on column dba_dependencies_ae_dh.TYPE is ‘Type of the object’;
comment on column dba_dependencies_ae_dh.EDITION_NAME is ‘Name of edition for which this object is active’;
comment on column dba_dependencies_ae_dh.OBJECT_ID is ‘ID of this object’;
comment on column dba_dependencies_ae_dh.REFERENCED_OWNER is ‘Owner of referenced object (remote owner if remote object)’;
comment on column dba_dependencies_ae_dh.REFERENCED_NAME is ‘Name of referenced object’;
comment on column dba_dependencies_ae_dh.REFERENCED_TYPE is ‘Type of referenced object’;
comment on column dba_dependencies_ae_dh.REFERENCED_EDITION_NAME is ‘Name of edition for which referenced object is active’;
comment on column dba_dependencies_ae_dh.REFERENCED_OBJECT_ID is ‘ID of referenced object’;
comment on column dba_dependencies_ae_dh.REFERENCED_LINK_NAME is ‘Name of dblink if this is a remote object’;
create or replace public synonym dba_dependencies_ae_dh for sys.dba_dependencies_ae_dh;

There are of course some changes to data dictionary views to make them understand editions. Often you’ll find a new column called “edition” or “edition_name” and that contains exactly what you’d expect. But in this new world where objects that used to be unique in their namespace can now be instantiated in multiple editions, how can you see what is in an edition other than the one your session is using?

That is the purpose of the “_ae” views. That stands for “all editions”, by the way. These views also contain the edition column that has appeared on the traditional views, but these views can contain multiple rows. I’ll use dba_objects and dba_objects_ae to illustrate this. The effective PK for dba_objects is owner + object_name + object_type, in other words, if you were to get counts grouping by these 3 columns, you would find them all to be 1. If you ran the same query on dba_objects_ae you might get some rows with counts > 1 because it contains rows from multiple editions. The edition shown in dba_objects rows will not always be the current edition of your session because you might be inheriting some objects from parent editions, but the edition name will never be a child edition of your current edition (children inherit from parents, never the other way). That is not true for dba_objects_ae, it will show you objects in editions that are children to your current edition, it ignores your current edition and shows you everything. A good analogy is to consider the difference between user_tables and dba_tables, the first filters out any tables you do not own, the second shows them all. Similarly, dba_objects filters out all objects that are not visible in your current edition, dba_objects_ae shows them all.

So for general purposes, use the views that you always have — they are your session’s “reality”, but to query objects across all the editions use the “_ae” views. They might seem rather pointless to you now, but get familiar with them anyway, because you will need them in the future.

You might also find that there are some missing “_ae” views. I have found two in particular that I needed and had to create myself. I will address those in future posts because they are large topics in themselves and need enough explanation to warrant their own post.

The editioning view (EV) is a new object type that sits between the table (which cannot be editioned) and the code objects that reference the table. It acts like a table in many ways – you can do DML without an instead of trigger, and you can create a normal trigger on the EV.

The main function of an EV is to allow you to alias columns, and to make those aliases editionable. It is functionally close to an editioned table. You can’t restrict rows or join tables in an EV. The set of allowed actions is really quite restrictive, but the ability to change the underlying column that a view column points to is a powerful feature that you will end up using quite a lot, so plan to switch all your tables to use EV’s in your conversion plans.

Converting a table to use an EV involves the following steps:

1. Save the source of all the triggers on the table

2. Generate and save the DDL for the grants to this table

3. Drop any public synonyms that point to the table

4. Rename the table – I added a ‘_’ to the end of each table. Any that failed because the resulting name was too long has the last char of the name replaced with a ‘_’. This is needed because the EV occupies the same Oracle namespace as the table, and you don’t want to have to change the name in all of your regular views and PL/SQL.

5. Create an EV for the table with the same name as the old table – “create editioning view mytable as select * from mytable_;”

6. Drop the triggers from the table and create them on the EV, using the source code you saved in step #1.

7. Run the grant DDL from step #2 to allow all the access to the EV that the original table had. Revoke the grants from the table, everyone must now use the EV.

8. Create private synonyms for any schemas that were using the public synonym. If you lack the time to determine this, you can create them for all the users. I addressed the synonym issues in an earlier post.

9. Recompile all the objects that are invalid.

When you are done the indexes and constraints still refer to the underlying table, but all of these will reference the EV:

triggers

views

PL/SQL program objects (packages, standalone procs and funcs)

synonyms

grants