Administration
Editions
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Tuesday, 27 July 2010 22:03
-
Last Updated on Friday, 04 October 2013 16:43
-
Published on Tuesday, 27 July 2010 22:03
-
Written by Guy Lambregts
-
Hits: 8435
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
2 begin
3 null;
4 end;
5 /
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
2 dat date;
3 begin
4 select sysdate into dat from dual;
5 end;
6 /
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;