Month: July 2013

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;