Skip to content

Edition-based redefinition allows multiple versions of PL/SQL objects.

December 24, 2013

Edition-based redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.

To upgrade an application while it is in use, you copy the database objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users.

Using edition-based redefinition means using one or more of its component features. The features you use, and the down time, depend on these factors:

  •     What kind of database objects you redefine
  •     How available the database objects must be to users while you are redefining them
  •     Whether you make the upgraded application available to some users while others continue to use the older version of the application

——————————————————————————————————————————————————

CREATE EDITION

Purpose

This statement creates a new edition as a child of an existing edition. An edition makes it possible to have two or more versions of the same editionable objects in the database. When you create an edition, it immediately inherits all of the editionable objects of its parent edition. The following object types are editionable:

  •     Synonym
  •     View
  •     Function
  •     Procedure
  •     Package (specification and body)
  •     Type (specification and body)
  •     Library
  •     Trigger

An editionable object is an object of one of the above editionable object types in an editions-enabled schema. The ability to have multiple versions of these objects in the database greatly facilitates online application upgrades.

Note:
All database object types not listed above are not editionable. Changes to object types that are not editionable are immediately visible across all editions in the database.

Every newly created or upgraded Oracle Database has one default edition named ORA$BASE, which serves as the parent of the first edition created with a CREATE EDITION statement. You can subsequently designate a user-defined edition as the database default edition using an ALTER DATABASE DEFAULT EDITION statement.

————————————————————————————————————————————————————

SQL> select VERSION from v$instance;

VERSION
—————–
11.2.0.1.0

SQL> select * from DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_EDITION’;

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
—————————— —————————— ———————————————————————-
DEFAULT_EDITION                ORA$BASE                       Name of the database default edition

SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to SCOTT;

Grant succeeded.

SQL> select USERNAME,EDITIONS_ENABLED from dba_users where username=’SCOTT’;

USERNAME                       E
—————————— –
SCOTT                          N

SQL> ALTER USER SCOTT ENABLE EDITIONS;

User altered.

SQL> select USERNAME,EDITIONS_ENABLED from dba_users where username=’SCOTT’;

USERNAME                       E
—————————— –
SCOTT                          Y

SQL> conn scott/tiger

SELECT SYS_CONTEXT
(‘userenv’,
‘current_edition_name’) sc
FROM DUAL;

SC
————–
ORA$BASE

Create PL/SQL procedure for this example:
===============================================
SQL> show user
USER is “SCOTT”

SQL> CREATE OR REPLACE PROCEDURE hello IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Hello, edition 1.’);
END hello;
/

Procedure created.

SQL> set serveroutput on

SQL> exec hello;

Hello, edition 1.

PL/SQL procedure successfully completed.

SQL> CREATE EDITION e2 AS CHILD OF ora$base;

Edition created.

SQL> select * from ALL_EDITIONS;

EDITION_NAME                   PARENT_EDITION_NAME            USA
—————————— —————————— —
ORA$BASE                                                      YES
E2                             ORA$BASE                       YES

SQL> ALTER SESSION SET EDITION = e2;

Session altered.

SQL> CREATE OR REPLACE PROCEDURE hello IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Hello, edition 2.’);
END hello;
/

Procedure created.

SQL> exec hello;
Hello, edition 2.

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET EDITION = ora$base;

Session altered.

SQL> exec hello;
Hello, edition 1.

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET EDITION = e2;

Session altered.

SQL> exec hello;
Hello, edition 2.

PL/SQL procedure successfully completed.

Make new edition available to all users (requires system privileges):
===========================================================================
ALTER DATABASE DEFAULT EDITION = e2;

Conn new session for SCOTT.
=======================================
SQL> conn scott/tiger
Connected.
SQL> set serveroutput on
SQL> exec hello;
Hello, edition 2.

PL/SQL procedure successfully completed.

SQL> drop PROCEDURE hello;

Procedure dropped.

SQL> ALTER SESSION SET EDITION = ORA$BASE;

Session altered.

SQL> exec hello;
Hello, edition 1.

PL/SQL procedure successfully completed.

SQL> DROP EDITION e2;

Edition dropped.

####################################################################################################

To Switch Between Editions
The ORA_EDITION environment variable determines which edition SQL*Plus will connect to.
####################################################################################################
# UNIX/Linux
export ORA_EDITION=ora\$base

OR

sqlplus username/password@service edition=ora\$base

###########For Windows###########
set ORA_EDITION=ora$base

set ORA_EDITION=E2
sqlplus scott/tiger
SQL> SELECT SYS_CONTEXT
2             (‘userenv’,
3              ‘current_edition_name’) sc
4           FROM DUAL;

SC
———————————————-
E2

CONNECT scott/tiger EDITION=E2

sqlplus scott/tiger EDITION=E2

####################
Dropping edition
####################

SQL> SELECT * FROM dba_editions;

EDITION_NAME                   PARENT_EDITION_NAME            USA
—————————— —————————— —
ORA$BASE                                                      YES
E2                             ORA$BASE                       YES

SQL> select * from DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_EDITION’;

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
—————————— —————————— —————————————————————
DEFAULT_EDITION                E2                             Name of the database default edition

SQL> drop edition e2;
drop edition e2
*
ERROR at line 1:
ORA-38805: edition is in use

SQL> ALTER DATABASE DEFAULT EDITION = ORA$BASE;

Database altered.

SQL>
SQL>  select * from DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_EDITION’;

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
—————————— —————————— —————————————————————
DEFAULT_EDITION                ORA$BASE                       Name of the database default edition

SQL>  drop edition e2;
drop edition e2
*
ERROR at line 1:
ORA-38805: edition is in use

SQL>select s.sid,s.serial#,s.username,s.SESSION_EDITION_ID,o.OBJECT_NAME,OBJECT_TYPE from v$session s, dba_objects o
where s.SESSION_EDITION_ID=o.OBJECT_ID
and username=’SCOTT’;

SID    SERIAL# USERNAME   SESSION_EDITION_ID OBJECT_NAME                    OBJECT_TYPE
—- ———- ———- —————— —————————— —————
191        253 SCOTT                   75364 E2                             EDITION

After killing the above session
==================================
SQL> SELECT * FROM dba_editions;

EDITION_NAME                   PARENT_EDITION_NAME            USA
—————————— —————————— —
ORA$BASE                                                      YES
E2                             ORA$BASE                       YES

SQL> drop edition e2;
drop edition e2
*
ERROR at line 1:
ORA-38811: need CASCADE option to drop edition that has actual objects

SQL> drop edition e2 cascade;

Edition dropped.

SQL> SELECT * FROM dba_editions;

EDITION_NAME                   PARENT_EDITION_NAME            USA
—————————— —————————— —
ORA$BASE                                                      YES

Services and Editions
======================
In 11.2.0.2 the CREATE_SERVICE and MODIFY_SERVICE procedures in the DBMS_SERVICE package have been modified to include an EDITION parameter allowing an edition to be linked to a service.

**** The AE lock is created for each user session connected to the database provided edition-based redefinition is enabled/installed. Each user session has one AE lock in shared mode, waiting to be pressed into service using the “ALTER SESSION SET EDITION=<edition_name>;” statement. This presumes the default database edition has been set by the DBA using “ALTER DATABASE DEFAULT EDITION = <edition_name>;”. Editions allow developers to change database object definitions in a private environment while the application is still in use, reserving the changes until the edition is put into service at the database level.
col DESCRIPTION for a70
col name for a40
col type for a10
select type,name,description from v$lock_type where type in (‘AE’, ‘TO’);

TYPE       NAME                                     DESCRIPTION
———- —————————————- ———————————————————————-
AE         Edition Lock                             Prevent Dropping an edition in use
TO         Temp Object                              Synchronizes DDL and DML operations on a temp object

col  OBJECT_TYPE for a15
col  OBJECT_NAME for a30
set line 200
col event for a30
SELECT l.sid,s.username, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER(‘&User’)
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;

Enter value for user: SCOTT

SID USERNAME      BLOCKER EVENT                          TYPE            LMODE    REQUEST OBJECT_NAME                    OBJECT_TYPE
—- ———- ———- —————————— ———- ———- ———- —————————— —————
73 SCOTT                 SQL*Net message from client    AE                  4          0 E2                     EDITION
191 SCOTT                 SQL*Net message from client    AE                  4          0 E2                     EDITION

References:

http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#ADFNS020
http://docs.oracle.com/cd/E24693_01/server.11203/e17118/statements_5009.htm
http://www.oracle-base.com/articles/11g/edition-based-redefinition-11gr2.php
http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#BABGGBID

Advertisements

From → Oracle

One Comment
  1. I have difficulty purging prior editions that clutter up my system: we deploy new code every 2 weeks, hence a new edition every time:

    ORA$BASE –> v37 –> v38 for example.

    I actualized all objects from ORA$BASE to v37, and for v38, same thing, actualized all v37 into v38

    then i tried this:

    DROP EDITION v37 cascade;

    and no luck.. got this:
    SQL Error: ORA-38810: Implementation restriction: cannot drop edition that has a parent and a child

    Any words of wisdom?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: