12 | 12 | 2017
Latest Articles
Popular Articles

Administration

Editions

User Rating:  / 0
PoorBest 

Editions

 

ARTICLE YET TO BE IMPROVED

 

Multiple object definitions (metadata) for the same object can coexist in the database. Editional object types are

 

1. Views

2. Procedures

3. Functions

4. Packages

5. Types

6. Library

7. Synonyms

8. Triggers

 

The idea is that one version of your object ddl is commonly in use. A second version can be compiled, tested, debugged at session level, ... and can be made active online, without library cache locks or whatsoever. The idea is smooth and transparent application migration ( I did not test this thoroughly though )

 

STEP 1 : We grant user student1 the right to "play" with editions

 

SQL> alter user student1 enable editions;

User altered.

SQL> grant create any edition, drop any edition to student1;

Grant succeeded.


STEP 2 : A first version of the procedure is compiled. ( in practise this code is somewhat more complex off course and exists since some time in the database )

 

SQL> connect student1/student1
Connected.


SQL> show user
USER is "STUDENT1"

SQL> create or replace procedure test_ed as
begin
null;
end;
/

Procedure created.

 

STEP 3 : the compatible parameter should be 11.2.

 

SQL> create edition test_edition;
*
ERROR at line 1:
ORA-00406: COMPATIBLE parameter needs to be 11.2.0.0.0 or greater
ORA-00722: Feature "Editions"


SQL> connect / as sysdba
Connected.

SQL> show parameter compatible

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
compatible                 string     11.1.0.0.0

SQL> alter system set compatible='11.2.0.0.0' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area  768352256 bytes
Fixed Size                  2217184 bytes
Variable Size             444599072 bytes
Database Buffers          314572800 bytes
Redo Buffers                6963200 bytes
Database mounted.
Database opened.

 

STEP 4 : An edition is created, The non default edition is made active at session level and a second version of the code is compiled. ( in real life this code is somewhat more complex off course )

 

SQL> connect student1/student1;
Connected.

SQL> create edition test_edition;

Edition created.

SQL> alter session set edition = test_edition;

Session altered.

SQL> create or replace procedure test_ed as
dat date;
begin
select sysdate into dat from dual;
end;
/

Procedure created.

 

STEP 5 : Edition switching at session level is possible. We notice the code difference.

 

SQL> set long 20000

SQL> select dbms_metadata.get_ddl('PROCEDURE','TEST_ED') from dual;

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

CREATE OR REPLACE PROCEDURE "STUDENT1"."TEST_ED" as
dat date;
begin
select sysdate into dat from dual;
end;



SQL> alter session set edition = ORA$BASE;

Session altered.

SQL> select dbms_metadata.get_ddl('PROCEDURE','TEST_ED') from dual;

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

CREATE OR REPLACE PROCEDURE "STUDENT1"."TEST_ED" as
begin
null;
end;


STEP 6 : The default edition at database level is ORA$BASE, this can be changed. ( note we never play with production database like I do here )

 

SQL> connect / as sysdba
Connected.

SQL> select property_value from database_properties where property_name='DEFAULT_EDITION';

PROPERTY_VALUE
--------------------------------------------------------------------------------
ORA$BASE


When the code compiled in edition test_edition may be "published" we can change the edition at database level. 

 

SQL> alter database default edition =  test_edition;


SQL> select property_value from database_properties where property_name='DEFAULT_EDITION';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEST_EDITION
 

 

SQL> connect student1/student1
Connected.

SQL> select dbms_metadata.get_ddl('PROCEDURE','TEST_ED','STUDENT1') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','TEST_ED','STUDENT1')
--------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE "STUDENT1"."TEST_ED" as
dat date;
begin
select sysdate into dat from dual;
end;



SQL> connect / as sysdba
Connected.

SQL> select dbms_metadata.get_ddl('PROCEDURE','TEST_ED','STUDENT1') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','TEST_ED','STUDENT1')
--------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE "STUDENT1"."TEST_ED" as
dat date;
begin
select sysdate into dat from dual;
end;