11.2.0.2 EDITION Attribute of a Database Service

I read 11g Release 2 (11.2.0.2) New Features and interested EDITION Attribute of a Database Service then tested it.

The EDITION attribute of a database service specifies the initial session edition for a session that is started using that service. If the program that creates a new session does not specify the initial session, then the edition name specified by the service is used. If the service does not specify the edition name, then the initial session edition is the database default edition.

When an edition-based redefinition exercise is implemented to support hot rollover, some clients to the database will want to use the pre-upgrade edition and others will want to use the post-upgrade edition. In this scenario, the database default edition is insufficient because, by definition, it denotes a single edition. The EDITION attribute of a database service provides a way to allow the client to specify the edition it wants using environment data rather than by changing the client code.

Before tested, I create procedure on ORA$BASE and VERSION2 editions.

SQL> connect demo

SQL> SELECT SYS_CONTEXT ('userenv','current_edition_name') ce FROM DUAL;

CE
--------------------------------------------------------------------------------
ORA$BASE

SQL> create or replace procedure my_procedure
as
begin
dbms_output.put_line ( 'I am version 1.0' );
end;
/

SQL> alter session set edition=version2;

Session altered.

SQL> SELECT SYS_CONTEXT ('userenv','current_edition_name') ce FROM DUAL;

CE
--------------------------------------------------------------------------------
VERSION2

SQL> create or replace procedure my_procedure
as
begin
dbms_output.put_line ( 'I am version 2.0' );
end;
/

then created and started new Database service by using DBMS_SERVICE

SQL> exec DBMS_SERVICE.CREATE_SERVICE(service_name => 'orcl_v2',network_name => 'orcl_v2', edition => 'VERSION2');

PL/SQL procedure successfully completed.

SQL> select edition from dba_services where name='orcl_v2';

EDITION
------------------------------
VERSION2

SQL> exec DBMS_SERVICE.START_SERVICE(service_name => 'orcl_v2', instance_name => 'orcl');

PL/SQL procedure successfully completed.

Check and test connect from Client.

$ lsnrctl service | grep orcl_v2
Service "orcl_v2" has 1 instance(s).

Note: tnsnames.ora file:
ORCL=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oratest)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

ORCL_V2=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oratest)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl_v2)))

Test:

SQL> connect demo@orcl
Enter password:
Connected.

SQL> SELECT SYS_CONTEXT ('userenv','current_edition_name') ce FROM DUAL;

CE
--------------------------------------------------------------------------------
ORA$BASE

SQL> exec my_procedure;
I am version 1.0

PL/SQL procedure successfully completed.

SQL> connect demo@orcl_v2
Enter password:
Connected.

SQL> SELECT SYS_CONTEXT ('userenv','current_edition_name') ce FROM DUAL;

CE
--------------------------------------------------------------------------------
VERSION2

SQL> exec my_procedure;
I am version 2.0

PL/SQL procedure successfully completed.

Reference: Oracle Documents
http://download.oracle.com/docs/cd/E11882_01/server.112/e17128/toc.htm
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_serv.htm