Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, SREEGURUPARAN.

Asked: October 29, 2009 - 2:04 pm UTC

Last updated: April 12, 2021 - 5:19 am UTC

Version: 11g r2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Can you explain, what is 'EDITION' and what is it meant as cross-edition triggers? How it can be useful for high availability.

Thanks for you replies. It is a big learning experience.

and we said...

I'll be addressing this in detail over the course of three Oracle Magazine issues starting with the Jan/Feb issue. It is a *large* topic and needs a bit of space to address completely.

I'll try to put it in a nutshell here. The forthcoming articles will be more comprehensive and example laden. For technical details right now:

http://www.oracle.com/pls/db112/search?remark=quick_search&word=edition+based+redefinition

will cover it.


In short, Edition Based Redefinition (EBR) is a new capability in 11g Release 2 that allows us to stage all of the changes to our application in the database (put in and compile new versions of stored procedures, triggers, grants on things, views, etc - without disturbing the existing code base - without contention - without blocking anyone). Instead of taking a one hour outage to install, compile and verify 100 new pieces of code out of 1,000 units in an application - the application administrator can now install, compile and verify those 100 units in the production database without disturbing the existing application. When they are ready - they can cut over to the newly installed code in one command (sort of like being able to transactionally install 100 things all at once).

So, in its simplest form - EBR allows you to remove the downtime involved in patching your application - you can safely put into place a new copy of code in the database in your production or development environment without blocking, without having to take an outage.


Going further, you can even use its capabilities to perform complete application upgrades that involve physical schema changes - such as adding a column, redefining a table, whatever. Here - you can use EBR to reduce the amount of down time necessary for the upgrade OR if you want to go all of the way, you can use EBR to eliminate the downtime.

This is where cross edition triggers come into play - as well as a thing called an editioning view.

Suppose you wanted to make a change to a column T.C1 - modify it from the datatype NUMBER to the datatype VARCHAR2(50) for whatever reason. Now we cannot just modify the column - because that would affect the existing application - it binds number types in queries like "select * from t where c1 = :x" and if we converted C1 to a varchar2 - we'd lose the ability to use the index on C1 for example as well as have other strange things happen. So, we cannot modify the column type while the current application is running.

But - we know we could add a column C1_new varchar2(50) to the table and with 11g Release 1's ability to 'fast add' a column - do it without affecting the current application. Then, we would want to update c1_new to contain the string value of c1. But, we would not just want to "update t set c1_new = to_char(c1)" for two reasons

a) that would lock the entire table, every row. That would not be an online change
b) once the update finished and the current version of the application began working again, newly created rows in T would not have the necessary data in c1_new.

So, this is where editioning views and crossedition triggers come into play. These are the steps we would take


a) every 'release' of our code would access an editioning view, NOT the base table. An editioning view is a view that acts just like a table, it is more like a synonym than a view - you can put triggers on it, do to it things you would do to a table. The application thinks this IS the table. The editioning view can only select and rename columns from a single table - no where clause, no joins, just a simple "select .... from table".

b) the current version of our application would have an editioning view T that selects C1 and the other columns

c) we would alter table T_real_table add c1_new varchar2(50) - current version does not see this column at all. It is unaffected by this change.

d) we would create a new editioning view in "version 2" - it would not overwrite the view in "current version", it would create a new one in the same schema. This new editioning view instance would select C1_new AS C1 - instead of selecting C1 itself. So, in the new release - we have C1 as a varchar2(50)

e) we would create a crossedition trigger - a trigger that fires ONLY IN ONE OF THE EDITIONS. We would create this crossedition trigger as a "forwarding" trigger - it would fire only if the code executing was in the "current version", it's goal would be to 'forward' the changes from 'current version' to 'version 2'. In this case, the trigger would look something like:

begin
:new.c1_new := to_char(:new.c1);
end;

it would fill in the data for version 2.


f) now, any new rows added in 'current version' would forward the new data to version 2. Any rows that had c1 updated would do the same - forward the change to version 2. All we need to do now is - in 'current version' - execute "update t set c1=c1" - that would fire this trigger and set the c1_new column properly. BUT - we don't want to do that - we need to do this in a way that will not lock the entire table.... so.....

g) we use the new package dbms_parallel_execute (if you want to see a quick example, grab my slides from Oracle OpenWorld 2009 - available on the files tab above). Using that package we can execute that update in the background in 1,000 pieces - that is, we break the table into 1,000 little pieces (each about 1/10th of a percent of the table) and have the update work just against that little bit of the table. At most we could have 1/10th of a percent of the table locked at a time - committing after each piece. This update - done in 'current version' would fire the trigger to move the data from c1 to c1_new.


Now, we can stage version 2 of our application (remember, 'current version' is still running, has been running, is not blocked, has not stopped so far). We put in place the new code, synonyms, etc as described above. The new data in c1_new is being maintained by the cross edition trigger - forwarding the change from 'current version' to version 2.

When all of the changes have taken place - all of the code is installed, verified, compiled, validated - we can 'cut over'.

We have significantly reduced the down time for this upgrade to just a short "cutover".

Once we cutover - we drop the forwarding trigger - 'current version' is dead, we don't need it anymore. We can then drop c1 from the table as well if you want (or not, up to you)





We could go a step further and do this upgrade entirely online - we would run 'current version' AND 'version 2' at the same time. We would REMOVE the downtime for the upgrade altogether. In order to accomplish this - we'd need another trigger - a reverse crossedition trigger - one that fires in 'version 2' and maintains the data in 'current version'. This may or may not be possible. If you look at this example, we are converting a number type to a varchar2 type - that always works, a number can become a string. The reverse is not necessarily true. If 'version 2' will immediately start adding values in C1 that cannot be converted into numbers - we cannot run with both of these editions, versions at the same time - they are incompatible. But let's assume that 'version 2' will continue to generate values for some period of time that are numbers and can be converted.

Then, all we would do is add the column, install the forwarding trigger, update the table by piece, add the code for 'version 2' (this is all online remember) and then install a reverse crossedition trigger for 'version 2', this one would have

begin
:new.c1 := to_number(:new.c1_new);
end;


Now, we can release 'version 2' - allow new sessions to use 'version 2' but permit existing connections to continue to use 'current version'. Both versions will be running. Over time - as connections in connection pools close and reopen themselves - 'current version' will cease being used.

We can then drop the crossedition triggers and clean up.



Again, as I wrote above, I'll be covering this in more detail in the magazine over the course of three issues - about 12-15 pages of stuff... But you can read the documentation and get the gist of the concept of EBR here.


Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 19, 2010 - 10:45 am UTC


Follow-up to your article in oracle magazine

A reader, May 27, 2010 - 10:38 am UTC

Tom,

Thanks for the articles in the magazine. This is really a great feature and I was in the process of performing a proof of concept for my customer, but I've run into a bit of a functionality gap that I was hoping you could clear up. In the March issue you indicate that to enable editions that you essentially perform 3 steps:

1. Rename the table
2. Create the edition view
3. Move the triggers from the table to the edition view

My environment is composed of many users that own various application components for the sake of this question I have two users: DBA_USER and APP_OWNER. The APP_OWNER is created with the following statement:

CREATE USER APP_OWNER IDENTIFIED BY PASSWORD1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS ACCOUNT LOCK
/

I proceed with the setup of the edition and granting appropriate access as the DBA_USER:

ALTER USER APP_OWNER ENABLE EDITIONS
/
CREATE EDITION V1 AS CHILD OF ORA$BASE
/
GRANT USE ON EDITION V1 TO APP_OWNER
/

The user has a CODE table (Note This table already exists the CREATE statement is to provide context) as defined by the following statement:

CREATE CODE (
code_id NUMBER(8) NOT NULL
,code_type VARCHAR2(8) NOT NULL
,code_name VARCHAR2(128) NOT NULL
,code_value VARCHAR2(256) NOT NULL
,description VARCHAR2(256)
) TABLESPACE USERS
/
ALTER TABLE CODE ADD CONSTRAINT CODE_PK PRIMARY KEY (CODE_ID)
/
ALTER TABLE CODE ADD CONSTRAINT CODE_NAME_UK UNIQUE (CODE_TYPE,CODE_NAME)
/

When I attempt to perform the step 1 that was outlined in the article, I would like to perform it from the DBA_USER account. However when I attempt this operation, I receive the following error:

RENAME APP_OWNER.CODE TO APP_OWNER.CODE1
/
Error starting at line 9 in command:
RENAME APP_OWNER.CODE TO APP_OWNER.CODE1
Error report:
SQL Error: ORA-01765: specifying owner's name of the table is not allowed
01765. 00000 - "specifying table's owner name is not allowed"
*Cause:
*Action:

So I did a google research and found an alternative statement, which offered some promise to working, but I had a faint recollection that it should not be used and can't remember why. I issued the following statement:

ALTER TABLE APP_OWNER.CODE RENAME TO APP_OWNER.CODE1
/

This resulted in the following:

Error starting at line 11 in command:
ALTER TABLE APP_OWNER.CODE RENAME TO APP_OWNER.CODE1
Error report:
SQL Error: ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations
14047. 00000 - "ALTER TABLE|INDEX RENAME may not be combined with other operations"
*Cause: ALTER TABLE or ALTER INDEX statement attempted to combine a RENAME operation with some other operation which is illegal
*Action: Ensure that RENAME operation is the sole operation specified in the ALTER TABLE or ALTER INDEX statement;

I'm assuming that this was a result of the associated indexes that were created as a result of the primary and unique constraints; removed them; and the command worked.

Since this solution is less than optimal, I was hoping that you could offer another option. My questions are as follows:

1. Is there another method to rename the tables without having to connect to the user and issue the command from his account?
2. Since renaming a table through the ALTER command is not the approach that you chose what are the other consequences of using this command?
3. Is there another way to use the ALTER command to result in the table rename without having to drop indexes.

Thank you for your comments in advance.
Tom Kyte
May 27, 2010 - 10:53 am UTC

ops$tkyte%ORA11GR2> alter table scott.dept rename to dept_new;

Table altered.

ops$tkyte%ORA11GR2> desc scott.dept_new;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 DEPTNO                                   NOT NULL NUMBER(2)
 DNAME                                             VARCHAR2(14)
 LOC                                               VARCHAR2(13)

ops$tkyte%ORA11GR2> alter table scott.dept_new rename to dept;

Table altered.


no schema name in the new name, not needed, it is implied.

Editioning View

Andrey, October 12, 2010 - 11:38 am UTC

Dear Tom,
What are the benefits of using an editioning view instead of a simple updatable view? I’ve found only this one – we can add triggers to an editioning view, but why this approach is better than just leaving them on the table level? It seems that triggers on editioning view do not invalidate when I add column to the base table. But we have problem with on delete trigger on editioning view and foreing key with on delete cascade option.

Thank you for your comments in advance

Tom Kyte
October 12, 2010 - 11:52 am UTC

editioning views are much like "a really fancy synonym" - you can use them to point to a table (a single table, only a table, there is no view text really) and pick the columns (and rename them if you like).

They are not really a 'view', they are really just a single table projection. They are lighter weight than a view. A view has to be merge (optimized) into the query - even if it is simple. An editioning view is known to just be an alias - nothing more.

And they can have triggers.


"Editions" feature is really a bug

Mikito Harakiri, December 16, 2010 - 4:59 pm UTC

Wouldn't the fact that oracle commits after every statement

<code>create or replace
procedure PROC1 as 
begin
 null;
end PROC1;
/
create or replace
procedure PROC2 as 
begin
 null;
end PROC2;
/
commit;


considered a bug? Imagine it's fixed: why do we need editions, then? </code>
Tom Kyte
December 16, 2010 - 9:25 pm UTC

No, it isn't. First, the exclamation that "Oracle commits after every statement" is false and misleading. DDL automatically commits before it is executed and commits or rollsback after it is done. Only DDL. Having DDL that didn't commit would lock up your system tighter than a drum, faster than you could believe.

Even if it didn't commit and was "lock free", it still wouldn't give you even half of what edition based redefinition does. The ability to have (and therefore test) two or more versions of your application. The ability to have that history of applications (what did the code look like six months ago). The ability to run two versions simultaneously for a hot rollover. The ability to facilitate physical schema changes in an online fashion. And so on.

I think you haven't looked deep enough here.

Switching all connected sessions to a new edition

Javi, January 16, 2013 - 3:01 am UTC

Hi Tom,

I want to know if is there any way to change the edition of all connected sessions.

With an "ALTER DATABASE DEFAULT EDITION = new_edition" I can change the default edition and then, all the new sessions will work with the new edition, but what happend with the session that are working already with the old edition?

I know I am able to change the edition of my session if I am connected to my DB via sqlplus (or toad, sqldeveloper,...) using "ALTER SESSION SET EDITION = new_edition" . But our customers connects to DB using a java or delphi interface and they uses stored procedures, so this sentence can't be executed.

For us, disconnecting some sessions could be very painfull so the solution of reconnect all user is not feasible.

I can change the edition of connected sessions if i have something like:

IF my_edition <> default_edition THEN
dbms_session.set_edition_deferred("default_edition");
END;

In all packages, functions and procedures. But I think that this practice isn't clean at all.

Could you tell me if is there a better way to do that?

Thanks for all.
Tom Kyte
January 16, 2013 - 12:05 pm UTC

I want to know if is there any way to change the edition of all connected
sessions.


no, there is not.


... I can change the edition of connected sessions if i have something like:
...

and that wouldn't work either because the change is *deferred*


with edition based redefinition you can either

a) mitigate/reduce downtime to a very very short period of time - seconds or less.

b) remove downtime altogether


However, in order to achieve (b) you have to be able to support a "hot rollover" period - a period of time during which version 1 and version 2 of your application will be concurrently running. All existing connections would continue to use version 1, new connections would use version 2 (hot rollover) - eventually, as connections drop off - version 1 won't be used anymore and you can drop that edition/cleanup.


but you cannot automagically tell all existing sessions to just start using version 2.



If you are using a connection pool, you can implement logic such that every time you grab a connection from the pool it would execute this set edition deferred call BEFORE returning the connection to the application (it could also call set identifier, set client info, set action/module and lots of other goodies too!) - but the application would have to do that.

ORACLE EDITION-BASED REDEFINITION

Javi, January 18, 2013 - 5:10 am UTC

Thanks for the request.

I Have another question about ORACLE EDITION-BASED REDEFINITION.

If we have a lot of editions(more than 20 editions), how it will afect to the DB? I guess that the size of the Data Base will be increased, but can be affected the performance of the database?

Thanks,
Javi.

Tom Kyte
January 18, 2013 - 10:28 am UTC

it might take marginally longer to resolve names. If you have a procedure P that was in version 1 and has never been changed, someone in version 20 would have to look backwards to find P when you call P.

I've never measured this however - but it is something that would happen at hard parse time, so as long as you have a system that was properly coded - it should not affect you too much.

Extract source by edition

Badri, June 12, 2013 - 8:09 am UTC

Hi Tom

Using editions we can see the different source code versions of the same object in all_source_ae view. Just wanted to know if there is any options available to accomplish the same thing using dbms_metadata.get_ddl function.

Thanks
Badri.
Tom Kyte
June 18, 2013 - 3:01 pm UTC

just set your edition. it'll get the right version

ops$tkyte%ORA11GR2> create user demo
  2     identified by demo;

User created.

ops$tkyte%ORA11GR2> grant create session,
  2        create procedure
  3     to demo;

Grant succeeded.

ops$tkyte%ORA11GR2> create edition version2
  2      as child of ora$base;

Edition created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect demo/demo
Connected.
demo%ORA11GR2> create or replace
  2  procedure my_procedure
  3  as
  4  begin
  5     dbms_output.put_line( 'I am buggy version 1.0' );
  6  end;
  7  /

Procedure created.

demo%ORA11GR2> create or replace
  2  procedure my_procedure2
  3  as
  4  begin
  5     my_procedure;
  6  end;
  7  /

Procedure created.

demo%ORA11GR2> pause

demo%ORA11GR2> 
demo%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> alter user demo
  2    enable editions;

User altered.

ops$tkyte%ORA11GR2> grant use
  2   on edition version2
  3   to demo;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect demo/demo
Connected.
demo%ORA11GR2> alter session
  2  set edition = version2;

Session altered.

demo%ORA11GR2> 
demo%ORA11GR2> create or replace
  2  procedure my_procedure
  3  as
  4  begin
  5     dbms_output.put_line( 'I am fixed in version 2.0' );
  6  end;
  7  /

Procedure created.

demo%ORA11GR2> 
demo%ORA11GR2> SELECT SYS_CONTEXT ('userenv', 'current_edition_name') sc
  2    FROM DUAL;

SC
-------------------------------------------------------------------------------
VERSION2

demo%ORA11GR2> select dbms_metadata.get_ddl( 'PROCEDURE', 'MY_PROCEDURE' ) from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','MY_PROCEDURE')
-------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "DEMO"."MY_PROCEDURE"
as
begin
   dbms_output.put_line( 'I am fixed in version 2.0' );
end;


demo%ORA11GR2> 
demo%ORA11GR2> connect demo/demo
Connected.
demo%ORA11GR2> SELECT SYS_CONTEXT ('userenv', 'current_edition_name') sc
  2    FROM DUAL;

SC
-------------------------------------------------------------------------------
ORA$BASE

demo%ORA11GR2> select dbms_metadata.get_ddl( 'PROCEDURE', 'MY_PROCEDURE' ) from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','MY_PROCEDURE')
-------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "DEMO"."MY_PROCEDURE"
as
begin
   dbms_output.put_line( 'I am buggy version 1.0' );
end;


demo%ORA11GR2> 

Extract Source by edition

Badri, July 01, 2013 - 8:14 am UTC

Thanks a lot Tom, for that explanation. Very USEFULL & INFORMATIVE forum to say the least!

Thanks again
Badri.

Forward Cross Edition Trigger

Gifta, March 20, 2015 - 11:50 am UTC

Hi Tom,

Your description regarding the EBR concepts are too good. I am analysing on FCET concepts. I have few doubts

1. FCET is defined on a table only if there is a DDL operation?
2. For testing purpose i have created a FCET in my run edition , taking your above example of converting the data type from NUMBER to VARCHAR, so my doubt is will it fire if i update my table in the run edition.

I know the above point does not happen in real time. In real time a trigger is created in the patch edition and it fires when there is any change in the run edition.
CHange in run edition do you mean a DML????

As usual, EBR Very well explained, Tom !!! Thanks!

Yogi P, October 31, 2016 - 3:09 pm UTC


Question

SSingh, April 10, 2021 - 6:19 am UTC

This blog is very informative.
I am not a dba, but I am researching ERB's for a project.I want to know , how does the view e.g applsys.fnd_flex_values# know that it should only look at 'set1' rows from the base table applsys.fnd_flex_values?
Which object in the database tells the editionable views that the active set has changed?
Connor McDonald
April 12, 2021 - 5:19 am UTC

Edition views let you have multiple version of the view that all point to the *same* table. The definition of the view can be used to determine what data it sees.

The table always contains *all* data and *all* columns that might be applicable to all ediitions.

More to Explore

DBMS_PARALLEL_EXECUTE

More on PL/SQL routine DBMS_PARALLEL_EXECUTE here