Oracle 11.2 introduced a new feature Editioning-Based Redefinition. I began to notice it only a few months ago when I looked into EBS 12.2, which claims to have a game-changer feature “online patching”, which essentially means EBS has to be taken off-line for a very short period of time to install patches. It certainly is very appealing to users.
The technique for installing patches containing only files is very easy: EBS 12.2 has two file systems, one is the running file system, and the other is the patching file system. Patch files are installed into the patch file system. The running and the patching file systems are switched back and forth.
The technique for installing patches that change database is entirely based on the Editioning-Based Redefinition of Oracle 11.2. As always, Tom explains this technique with a level of clarity that only someone very intelligent, very knowledge can manage:
Edition-Based Redefinition, Part 2
Looking at Edition-Based Redefinition, Part 3
But this time, I found it hard to wrap my mind around it, it seems a rather confusing technique to get used to – maybe that is why I haven’t saw usages of this technique before, and only begun to know it recently. I followed the instructions step by step, and drawn a few diagrams to help me understand it.
This blog is to show how to editioning a stored procedure. The next blog will be about editioning a table.
The big picture
Starting Oracle 11.2, there is a default edition called ORA$BASE. You can create a child edition of ORA$BASE, a child edition starts its life as an exact copy of ORA$BASE.
In this example, my_procedures uses my_procedure. Version2 is a child version of ORA$BASE. Within version2, my_procedure is changed, my_procedure2 is not, so my_procedure2 is
the same as its base version. Because the change to my_procedure happens inside version2, the user my_procedure2 in ORA$BASE is not affected. So in ORA$BASE, my_procedure2 continues to use the old version of my_procedure; in version2, my_procedure2 uses the new version of my_procedure.
Create demo user
--use sys accountcreate user demo identified by demo;grant create table, create view, create sequence, create trigger, create procedure, create session to demo;
--use sys accountcreate edition version2 as child of ora$base;--demo account is edition-enabled, meaning, it can change objects within the editionalter user demo enable editions;grant use on edition version2 to demo;--make user scott able to use version2grant use on edition version2 to scott;
Create procedures in the base edition
--use demo accountset serveroutput oncreate or replace procedure my_procedureasbegindbms_output.put_line( 'I am version 1.0' );end;/create or replace procedure my_procedure2asbeginmy_procedure;end;/
Change my_procedure in version2
--use demo account--switch to version 2alter session set edition = version2;create or replace procedure my_procedureasbegindbms_output.put_line( 'I am version 2.0' );end;/
Now we are in the state of the diagram:
We can check out that my procedure2 works in both editions:
SQL> alter session set edition=version2;Session altered.SQL> exec my_procedure2();I am version 2.0SQL> alter session set edition = ORA$Base;Session altered.SQL> exec my_procedure2();I am version 1.0--check the current editionSQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;EDITION-----------ORA$BASE
What scott can see?
Recall scott can use version2 thanks to this statement:
--make user scott able to use version2grant use on edition version2 to scott;
let us first ask user demo to grant scott the privilege to use my_procedure2:
--use demo account--switch to version 2alter session set edition = version2;grant execute on my_procedure2 to scott;
After this change, the status now becomes:
MY_PROCEDURE2 has a different version in version2 now. You can check this out with via user_objects, to check out all user objects, use user_objects_AE:
SQL> select object_name, edition_name from user_objects where object_name like 'MY_%';OBJECT_NAME EDITION_NAME------------------------------MY_PROCEDURE VERSION2MY_PROCEDURE2 VERSION2SQL> select object_name, edition_name from user_objects_AE where object_name like 'MY_%';OBJECT_NAME EDITION_NAME------------------------------MY_PROCEDURE ORA$BASEMY_PROCEDURE VERSION2MY_PROCEDURE2 ORA$BASEMY_PROCEDURE2 VERSION2
Let us log on as scott:
sqlplus scott/scottSQL> set serveroutput on;--scott is not able to see demo.my_procedure2();in base versionSQL> exec demo.my_procedure2();BEGIN demo.my_procedure2(); END;*ERROR at line 1:ORA-06550: line 1, column 7:PLS-00201: identifier 'DEMO.MY_PROCEDURE2' must be declaredORA-06550: line 1, column 7:PL/SQL: Statement ignoredSQL> alter session set edition = version2;Session altered.--scott is able to see my_procedure2 in version2SQL> exec demo.my_procedure2();I am version 2.0
All The following object types are editionable:
- Views (including editioning views, which we’ll define below)
- All PL/SQL object types (functions, procedures, packages, and so on)
But what about tables?