19 Edition-Based Redefinition

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

You always use the edition feature to copy the database objects and redefine the copied objects in isolation; that is why the procedure that this chapter describes for upgrading applications online is called edition-based redefinition.

If the object type of every object you will redefine is editionable (defined in "Editionable and Noneditionable Schema Object Types"), the edition is the only feature you use.

Table is not an editionable type. If you change the structure of one or more tables, you also use the editioning view feature.

If other users must be able to change data in the tables while you are changing their structure, you also use forward crossedition triggers. If the pre- and post-upgrade applications will be in ordinary use at the same time (hot rollover), you also use reverse crossedition triggers. Crossedition triggers are not a permanent part of the application—you drop them when all users are using the post-upgrade application.

Topics:

Editions

Editions are nonschema objects; as such, they do not have owners. Editions are created in a single namespace, and multiple editions can coexist in the database.

The database must have at least one edition. Every newly created or upgraded Oracle Database starts with one edition named ora$base.

Topics:

Editioned and Noneditioned Objects

An editioned object is a schema object that has both an editionable type and an editions-enabled owner. (A schema object that has an editionable type but not an editions-enabled owner is potentially editioned.) An edition can have its own copy of an editioned object, in which case only the copy is visible to the edition.

A noneditioned object is a schema object that has a noneditionable type. An edition cannot have its own copy of a noneditioned object. A noneditioned object is identical in, and visible to, all editions.

An editioned object is uniquely identified by its OBJECT_NAME, OWNER, and EDITION_NAME. A noneditioned object is uniquely identified by its OBJECT_NAME and OWNER—its EDITION_NAME is NULL. (Strictly speaking, the NAMESPACE of an object is also required to uniquely identify the object, but you can ignore this fact, because any statement that references the object implicitly or explicitly specifies its NAMESPACE.)

You can display the OBJECT_NAME, OWNER, and EDITION_NAME of an object with the static data dictionary views *_OBJECTS and *_OBJECTS_AE (described in Table 19-1).

You need not know the EDITION_NAME of an object to refer to that object (and if you do know it, you cannot specify it). The context of the reference implicitly specifies the edition. If the context is a data definition language (DDL) statement, the edition is the current edition of the session that issued the command (for information about the current edition, see "Current Edition and Session Edition"). If the context is source code, the edition is the one in which the object is actual (see "Inherited and Actual Objects").

Topics:

Editionable and Noneditionable Schema Object Types

These schema objects types are editionable:

  • SYNONYM

  • VIEW

  • All PL/SQL object types:

    • FUNCTION

    • LIBRARY

    • PACKAGE and PACKAGE BODY

    • PROCEDURE

    • TRIGGER

    • TYPE and TYPE BODY

All other schema object types are noneditionable. Table is an example of an noneditionable type.

A schema object of an editionable type is editioned if its owner is editions-enabled; otherwise, it is potentially editioned.

A schema object of a noneditionable type is always noneditioned, even if its owner is editions-enabled. A table is an example of an noneditioned object.

Note:

There is one exception to the rules: Although SYNONYM is an editionable type, a public synonym is a noneditioned object.

Rules for Editioned Objects

  • A noneditioned object cannot depend on an editioned object.

    For example:

    • A public synonym cannot refer to an editioned object.

    • A function-based index cannot depend on an editioned function.

    • A materialized view cannot depend on an editioned view.

    • A table cannot have a column of a user-defined data type (collection or Abstract Data Type (ADT)) whose owner is editions-enabled.

    • A noneditioned subprogram cannot have a static reference to a subprogram whose owner is editions-enabled.

    For the reason for this rule, see "Actualizing Referenced Objects".

  • An ADT cannot be both editioned and evolved.

    For information about type evolution, see Oracle Database Object-Relational Developer's Guide.

  • An editioned object cannot be the starting or ending point of a FOREIGN KEY constraint.

    The only editioned object that this rule affects is an editioned view. An editioned view can be either an ordinary view or an editioning view.

Enabling Editions for a User

To enable editions for a user, use the ENABLE EDITIONS clause of either the CREATE USER or ALTER USER statement.

The EDITIONS_ENABLED column of the static data dictionary view DBA_USERS or USER_USERS shows which users have editions enabled.

Enabling editions is retroactive and irreversible. When a user is editions-enabled, every editionable-type object that the user has owned or will own is an editioned object. You cannot enable editions for a user who owns a potentially editioned object with noneditioned dependents unless you specify FORCE:

ALTER USER user_name ENABLE EDITIONS FORCE;

FORCE is useful in the following situation: You must editions-enable users A and B. User A owns potentially editioned objects a1 and a2. User B owns potentially editioned objects b1 and b2. Object a1 depends on object b1. Object b2 depends on object a2. Editions-enable users A and B like this:

  1. Using FORCE, enable editions for user A:

    ALTER USER A ENABLE EDITIONS FORCE;
    

    Now a1 and a2 are editioned objects, and b2 (which depends on a2) is invalid.

  2. Enable editions for user B:

    ALTER USER B ENABLE EDITIONS;
    
  3. Recompile b2, using the appropriate ALTER statement with COMPILE. For a PL/SQL object, also specify REUSE SETTINGS.

    For example, if b2 is a procedure, use this statement:

    ALTER PROCEDURE b2 COMPILE REUSE SETTINGS
    

    For information about the ALTER statements for PL/SQL objects, see Oracle Database PL/SQL Language Reference.

    For information about the ALTER statements for SQL objects, see Oracle Database SQL Language Reference.

FORCE is unnecessary in the following situation: You must editions-enable user C, who owns potentially editioned object c1. Object c1 has dependent d1, a potentially editioned object owned by user D. User D owns no potentially editioned objects that have dependents owned by C. If you editions-enable D first, making d1 an editioned object, then you can editions-enable C without violating the rule that a noneditioned object cannot depend on an editioned object.

You cannot enable editions for a user who owns one or more evolved ADTs. Trying to do so causes error ORA-38820. If an ADT has no table dependents, you can use the ALTER TYPE RESET statement to reset its version to 1, so that it is no longer considered to be evolved. (Resetting the version of an ADT to 1 invalidates its dependents.)

See Also:

Creating an Edition

To create an edition, use the SQL statement CREATE EDITION.

You must create the edition as the child of an existing edition. The parent of the first edition created with a CREATE EDITION statement is ora$base. This statement creates the edition e2 as the child of ora$base:

CREATE EDITION e2

(Example 19-1 and others use the preceding statement.)

At Oracle Database 11g Release 2, an edition can have at most one child.

The descendents of an edition are its child, its child's child, and so on. The ancestors of an edition are its parent, its parent's parent, and so on. The root edition has no parent, and a leaf edition has no child.

See Also:

Oracle Database SQL Language Reference for information about the CREATE EDITION statement, including the privileges required to use it

Inherited and Actual Objects

Each database session uses exactly one edition at a time. Upon creation, a child edition inherits from its parent edition all editioned objects in the database that are visible in the parent edition. Each inherited object is visible in the child edition.

An inherited object is copied on change or actualized; that is, when a user of the child edition references an inherited object in a DDL statement (other than DROP), the inherited object is copied and the DDL statement affects only the copy—the actual object. The unchanged object in the parent edition is no longer visible in the child edition.

Note:

When the DDL statement CREATE OR REPLACE object has no effect, it does not actualize object (for details, see "Invalidation of Dependent Objects"). The DDL statement ALTER object COMPILE always actualizes object.

Example 19-1 creates a procedure named hello in the edition ora$base, and then creates the edition e2 as a child of ora$base. When e2 invokes hello, it invokes the inherited procedure. Then e2 changes hello, actualizing it. The procedure hello in the edition ora$base remains unchanged, and is no longer visible in e2. Now when e2 invokes hello, it invokes the actual procedure.

Example 19-1 Inherited and Actual Objects

  1. Create procedure in parent edition:

    CREATE OR REPLACE PROCEDURE hello IS
      BEGIN
        DBMS_OUTPUT.PUT_LINE('Hello, edition 1.');
      END hello;
    /
    
  2. Invoke procedure in parent edition:

    BEGIN hello(); END;
    /
    

    Result:

    Hello, edition 1.
     
    PL/SQL procedure successfully completed.
    
  3. Create child edition:

    CREATE EDITION e2;
    
  4. Use child edition:

    ALTER SESSION SET EDITION = e2;
    

    For information about ALTER SESSION SET EDITION, see "Changing Your Session Edition".

  5. In child edition, invoke procedure:

    BEGIN hello(); END;
    /
    

    Child edition inherits procedure from parent edition. Child edition invokes inherited procedure. Result:

    Hello, edition 1.
     
    PL/SQL procedure successfully completed.
    
  6. Change procedure in child edition:

    CREATE OR REPLACE PROCEDURE hello IS
      BEGIN
        DBMS_OUTPUT.PUT_LINE('Hello, edition 2.');
      END hello;
    /
    

    Child changes only its own copy of procedure. Child's copy is an actual object.

  7. Invoke procedure:

    BEGIN hello(); END;
    /
    

    Child invokes its own copy, the actual procedure:

    Hello, edition 2.
    
    PL/SQL procedure successfully completed.
    
  8. Return to parent:

    ALTER SESSION SET EDITION = ora$base;
    
  9. Invoke procedure and see that it has not changed:

    BEGIN hello(); END;
    /
    

    Result:

    Hello, edition 1.
     
    PL/SQL procedure successfully completed.
    

Topics:

Dropping Inherited Objects

If a user of the child edition drops an inherited object, that object is no longer visible in the child edition, but it continues to be visible in the parent edition.

Example 19-2 creates a procedure named goodbye in the edition ora$base, and then creates edition e2 as a child of ora$base. After e2 drops goodbye, it can no longer invoke it, but ora$base can still invoke it. (For more information about the DROP PROCEDURE statement, including the privileges required to use it, see Oracle Database PL/SQL Language Reference.)

Example 19-2 Dropping an Inherited Object

  1. Create procedure in edition ora$base:

    CREATE OR REPLACE PROCEDURE goodbye IS
      BEGIN
        DBMS_OUTPUT.PUT_LINE('Good-bye!');
      END goodbye;
    /
    
  2. Invoke procedure:

    BEGIN goodbye; END;
    /
    

    Result:

    Good-bye!
     
    PL/SQL procedure successfully completed.
    
  3. Create edition e2 as a child of ora$base:

    CREATE EDITION e2;
    
  4. Use edition e2:

    ALTER SESSION SET EDITION = e2;
    

    ALTER SESSION SET EDITION must be a top-level SQL statement. For more information, see "Changing Your Session Edition".

  5. In e2, invoke procedure:

    BEGIN goodbye; END;
    /
    

    e2 invokes inherited procedure:

    Good-bye!
     
    PL/SQL procedure successfully completed.
    
  6. In e2, drop procedure:

    DROP PROCEDURE goodbye;
    
  7. In e2, try to invoke dropped procedure:

    BEGIN goodbye; END;
    /
    

    Result:

    BEGIN goodbye; END;
          *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00201: identifier 'GOODBYE' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    
  8. Return to parent:

    ALTER SESSION SET EDITION = ora$base;
    
  9. In parent, invoke procedure:

    BEGIN goodbye; END;
    /
    

    Result:

    Good-bye!
     
    PL/SQL procedure successfully completed.
    

Because e2 dropped the procedure goodbye:

  • Its descendents do not inherit the procedure goodbye.

  • No object named goodbye is visible in e2, so e2 can create an object named goodbye of any editionable type. If e2 creates this object, the descendents of e2 inherit that object.

In Example 19-3, e2 creates a function named goodbye and then an edition named e3 as a child of e2. When e3 tries to invoke the procedure goodbye (which e2 dropped), an error occurs, but e3 successfully invokes the function goodbye (which e2 created).

Example 19-3 Creating an Object with the Name of a Dropped Inherited Object

  1. Return to e2:

    ALTER SESSION SET EDITION = e2;
    

    For information about ALTER SESSION SET EDITION, see "Changing Your Session Edition".

  2. In e2, create function named goodbye:

    CREATE OR REPLACE FUNCTION goodbye
      RETURN BOOLEAN
    IS
    BEGIN
      RETURN(TRUE);
    END goodbye;
    /
    
  3. Create edition e3:

    CREATE EDITION e3 AS CHILD OF e2;
    
  4. Use edition e3:

    ALTER SESSION SET EDITION = e3;
    
  5. In e3, try to invoke procedure goodbye:

    BEGIN
      goodbye;
    END;
    /
    

    Result:

      goodbye;
      *
    ERROR at line 2:
    ORA-06550: line 2, column 3:
    PLS-00221: 'GOODBYE' is not a procedure or is undefined
    ORA-06550: line 2, column 3:
    PL/SQL: Statement ignored
    
  6. In e3, invoke function goodbye:

    BEGIN
      IF goodbye THEN
        DBMS_OUTPUT.PUT_LINE('Good-bye!');
      END IF;
    END;
    /
    

    Result:

    Good-bye!
     
    PL/SQL procedure successfully completed.
    

Actualizing Referenced Objects

When a referenced object is actualized in an edition, all of its dependents (direct and indirect) that are not yet actualized in that edition become actualized in that edition in an invalid state. Therefore, an editioned object cannot have dependents that cannot be actualized. In other words, a noneditioned object cannot depend on an editioned object (for examples, see "Rules for Editioned Objects").

When an invalid object is referenced, the database automatically validates it, which requires name resolution. The database looks for the object name first in the current edition, then in the parent edition, and so on.

See Also:

Chapter 18, "Schema Object Dependency," for general information about dependencies among schema objects, including invalidation, revalidation, and name resolution

Making an Edition Available to Some Users

As the creator of the edition, you automatically have the USE privilege WITH GRANT OPTION on it. To grant the USE privilege on the edition to other users, use the SQL statement GRANT USE ON EDITION. For information about the GRANT statement, see Oracle Database SQL Language Reference.

Making an Edition Available to All Users

To make an edition available to all users, either:

  • Grant the USE privilege on the edition to PUBLIC:

    GRANT USE ON EDITION edition_name TO PUBLIC
    

    For information about the GRANT statement, see Oracle Database SQL Language Reference.

  • Make the edition the database default edition:

    ALTER DATABASE DEFAULT EDITION = edition_name
    

    This has the side effect of granting the USE privilege on edition_name to PUBLIC.

    For information about the ALTER DATABASE statement, see Oracle Database SQL Language Reference.

Current Edition and Session Edition

Each database session uses exactly one edition at a time. The edition that a database session is using at any one time is called its current edition. When a database session begins, its current edition is its session edition, which is the edition in which it begins. If you change the session edition, the current edition changes to the same thing. However, there are situations in which the current edition and session edition differ.

Topics:

Your Initial Session Edition

When you connect to the database, you can specify your initial session edition. Your initial session edition can be any edition on which you have the USE privilege. To see the names of the editions that are available to you, use this query:

SELECT EDITION_NAME FROM ALL_EDITIONS;

How you specify your initial session edition at connection time depends on how you connect to the database—see the documentation for your interface.

See Also:

As of Oracle Database 11g Release 2 (11.2.0.2), if you do not specify your session edition at connection time, then:

  • If you use a database service to connect to the database, and an initial session edition was specified for that service, then the initial session edition for the service is your initial session edition.

  • Otherwise, your initial session edition is the database default edition.

As of Release 11.2.0.2, when you create or modify a database service, you can specify its initial session edition.

To create or modify a database service, Oracle recommends using the srvctl add service or srvctl modify service command. To specify the default initial session edition of the service, use the -t option.

Alternatively, you can create or modify a database service with the DBMS_SERVICE.CREATE_SERVICE or DBMS_SERVICE.MODIFY_SERVICE procedure, and specify the default initial session edition of the service with the EDITION attribute.

Note:

As of Oracle Database 11g Release 2, the DBMS_SERVICE.CREATE_SERVICE and DBMS_SERVICE.MODIFY_SERVICE procedures are deprecated in databases managed by Oracle Clusterware and Oracle Restart.

See Also:

Changing Your Session Edition

After connecting to the database, you can change your session edition with the SQL statement ALTER SESSION SET EDITION. You can change your session edition to any edition on which you have the USE privilege. When you change your session edition, your current edition changes to the same thing.

These statements from Example 19-1 and Example 19-2 change the session edition (and current edition) first to e2 and later to ora$base:

ALTER SESSION SET EDITION = e2
...
ALTER SESSION SET EDITION = ora$base

Note:

ALTER SESSION SET EDITION must be a top-level SQL statement. To defer an edition change (in a logon trigger, for example), use the DBMS_SESSION.SET_EDITION_DEFERRED procedure.

See Also:

Displaying the Names of the Current and Session Editions

This statement returns the name of the current edition:

SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') FROM DUAL;

This statement returns the name of the session edition:

SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') FROM DUAL;

See Also:

Oracle Database SQL Language Reference for more information about the SYS_CONTEXT function

When the Current Edition Might Differ from the Session Edition

The current edition might differ from the session edition in these situations:

Example 19-4 creates a function that returns the names of the session edition and current edition. Then it creates a child edition, which invokes the function twice. The first time, the session edition and current edition are the same. The second time, they are not, because a different edition is passed as a parameter to the DBMS_SQL.PARSE procedure.

Example 19-4 Current Edition Differs from Session Edition

  1. Create function that returns the names of the session edition and current edition:

    CREATE OR REPLACE FUNCTION session_and_current_editions
      RETURN VARCHAR2
    IS
    BEGIN
      RETURN
      'Session: '|| SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') ||
      ' / ' ||
      'Current: '|| SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME');
    END session_and_current_editions;
    /
    
  2. Create child edition:

    CREATE EDITION e2 AS CHILD OF ora$base;
    
  3. Use child edition:

    ALTER SESSION SET EDITION = e2;
    
  4. Invoke function:

    BEGIN
      DBMS_OUTPUT.PUT_LINE (session_and_current_editions());
    END;
    /
    

    Result:

    Session: E2 / Current: E2
     
    PL/SQL procedure successfully completed.
    
  5. Invoke function again:

    DECLARE
      c     NUMBER := DBMS_SQL.OPEN_CURSOR();
      v     VARCHAR2(200);
      dummy NUMBER;
      stmt  CONSTANT VARCHAR2(32767)
        := 'SELECT session_and_current_editions() FROM DUAL';
    BEGIN
      DBMS_SQL.PARSE (c => c,
                      statement => stmt,
                      language_flag => DBMS_SQL.NATIVE,
                      edition => 'ora$base');
     
      DBMS_SQL.DEFINE_COLUMN (c, 1, v, 200);
      dummy := DBMS_SQL.EXECUTE_AND_FETCH (c, true);
      DBMS_SQL.COLUMN_VALUE (c, 1, v);
      DBMS_SQL.CLOSE_CURSOR(c);
      DBMS_OUTPUT.PUT_LINE (v);
    END;
    /
    

    Result:

    Session: E2 / Current: ORA$BASE
     
    PL/SQL procedure successfully completed.
    

Retiring an Edition

After making a new edition (an upgraded application) available to all users, you want to retire the old edition (the original application), so that no user except SYS can use the retired edition.

Note:

If the old edition is the database default edition, make another edition the database default edition before you retire the old edition:
ALTER DATABASE DEFAULT EDITION = edition_name

For information about the ALTER DATABASE statement, see Oracle Database SQL Language Reference.

To retire an edition, you must revoke the USE privilege on the edition from every grantee. To list the grantees, use this query, where :e is a placeholder for the name of the edition to be dropped:

SELECT GRANTEE, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = :e
/

For information about the REVOKE statement, see Oracle Database SQL Language Reference.

Dropping an Edition

Note:

If the edition includes crossedition triggers, see "Dropping the Crossedition Triggers" before you drop the edition.

To drop an edition, use the DROP EDITION statement, described in Oracle Database SQL Language Reference. If the edition has actual objects, you must specify the CASCADE clause, which drops the actual objects.

If a DROP EDITION edition CASCADE statement is interrupted before finishing normally (from a power failure, for example), the static data dictionary view *_EDITIONS shows that the value of USABLE for edition is NO. The only operation that you can perform on such an unusable edition is DROP EDITION CASCADE.

You drop an edition in these situations:

  • You want to roll back the application upgrade.

  • (Optional) You have retired the edition.

You can drop an edition only if all of these statements are true:

  • The edition is either the root edition or a leaf edition.

  • If the edition is the root, it has no objects that its descendents inherit. (That is, each object inherited from the root edition was either actualized or dropped.)

  • The edition is not in use (that is, it is not the current edition or session edition of a session).

  • The edition is not the database default edition.

To explicitly actualize an inherited object in the child edition:

  1. Make the child edition your session edition.

    For instructions, see "Changing Your Session Edition".

  2. Recompile the object, using the appropriate ALTER statement with COMPILE. For a PL/SQL object, also specify REUSE SETTINGS.

    For example, this statement actualizes the procedure p1:

    ALTER PROCEDURE p1 COMPILE REUSE SETTINGS
    

    For information about the ALTER statements for PL/SQL objects, see Oracle Database PL/SQL Language Reference.

    For information about the ALTER statements for SQL objects, see Oracle Database SQL Language Reference.

See Also:

Editioning Views

The owner of an editioning view must be editions-enabled before the editioning view is created.

On a noneditioning view, the only type of trigger that you can define is an INSTEAD OF trigger. On an editioning view, you can define every type of trigger that you can define on a table (except crossedition triggers, which are temporary, and INSTEAD OF triggers). Therefore, and because they can be editioned, editioning views let you treat their base tables as if the base tables were editioned. However, you cannot add indexes or constraints to an editioning view; if your upgraded application requires new indexes or constraints, you must add them to the base table.

An editioning view selects a subset of the columns from a single base table and, optionally, provides aliases for them. In providing aliases, the editioning view maps physical column names (used by the base table) to logical column names (used by the application). An editioning view is like an API for a table.

There is no performance penalty for accessing a table through an editioning view, rather than directly. That is, if a SQL SELECT, INSERT, UPDATE, DELETE, or MERGE statement uses one or more editioning views, one or more times, and you replace each editioning view name with the name of its base table and adjust the column names if necessary, performance does not change.

The static data dictionary view *_EDITIONING_VIEWS describes every editioning view in the database that is visible (actual or inherited) in the session edition. *_EDITIONING_VIEWS_AE describes every actual object in every editioning view in the database, in every edition.

Topics:

See Also:

Oracle Database Reference for more information about the static data dictionary views *_EDITIONING_VIEWS and *_EDITIONING_VIEWS_AE.

Creating an Editioning View

To create an editioning view, use the SQL statement CREATE VIEW with the keyword EDITIONING. To make the editioning view read-only, specify WITH READ ONLY; to make it read-write, omit WITH READ ONLY.

If an editioning view is read-only, users of the unchanged application can see the data in the base table, but cannot change it. The base table has semi-availability. Semi-availability is acceptable for applications such as online dictionaries, which users read but do not change. Make the editioning view read-only if you do not define crossedition triggers on the base table.

If an editioning view is read-write, users of the unchanged application can both see and change the data in the base table. The base table has maximum availability. Maximum availability is required for applications such as online stores, where users submit purchase orders. If you define crossedition triggers on the base table, make the editioning view read-write.

Because an editioning view must do no more than select a subset of the columns from the base table and provide aliases for them, the CREATE VIEW statement that creates an editioning view has restrictions. Violating the restrictions causes the creation of the view to fail, even if you specify FORCE.

See Also:

Oracle Database SQL Language Reference for more information about using the CREATE VIEW statement to create editioning views, including the restrictions

Partition-Extended Editioning View Names

An editioning view defined on a partitioned table can have a partition-extended name, with partition and subpartition names that refer to the partitions and subpartitions of the base table.

The data manipulation language (DML) statements that support partition-extended table names also support partition-extended editioning view names. These statements are:

  • DELETE

  • INSERT

  • SELECT

  • UPDATE

See Also:

Oracle Database SQL Language Reference for information about referring to partitioned tables

Changing the 'Write-ability' of an Editioning View

To change an existing editioning view from read-only to read-write, use the SQL statement ALTER VIEW READ WRITE. To change an existing editioning view from read-write to read-only, use the SQL statement ALTER VIEW READ ONLY.

See Also:

Oracle Database SQL Language Reference for more information about the ALTER VIEW statement

Replacing an Editioning View

To replace an editioning view, use the SQL statement CREATE VIEW with the OR REPLACE clause and the keyword EDITIONING.

You can replace an editioning view only with another editioning view. Any triggers defined on the replaced editioning view are retained.

Dropping or Renaming the Base Table

If you drop or rename the base table on which an editioning view is defined, the editioning view is not dropped, but the editioning view and its dependents become invalid. However, any triggers defined on the editioning view remain.

Adding Indexes and Constraints to the Base Table

If your upgraded application requires new indexes or constraints, you must add them to the base table. You cannot add them to the editioning view.

If the new indexes might negatively impact the old edition (the original application), make them invisible. In the crossedition triggers that must use the new indexes, specify them in INDEX hints.

When all users are using only the upgraded application:

  • If the new indexes were used only by the crossedition triggers, drop them.

  • If the new indexes are helpful in the upgraded application, make them visible.

See Also:

SQL Optimizer Index Hints

SQL optimizer index hints are specified in terms of the logical names of the columns participating in the index. Any SQL optimizer index hints specified on an editioning view using logical column names must be mapped to an index on the corresponding physical column in the base table.

See Also:

Oracle Database SQL Language Reference for information about using hints

Crossedition Triggers

The most important difference between crossedition triggers and noncrossedition triggers is how they interact with editions. A crossedition trigger is visible only in the edition in which it is actual, never in a descendent edition. Forward crossedition triggers move data from columns used by the old edition to columns used by the new edition; reverse crossedition triggers do the reverse.

Other important differences are:

  • Crossedition triggers can be ordered with triggers defined on other tables, while noncrossedition triggers can be ordered only with other triggers defined on the same table.

  • Crossedition triggers are temporary—you drop them after you have made the restructured tables available to all users.

Topics:

See Also:

Oracle Database PL/SQL Language Reference for general information about triggers

Forward Crossedition Triggers

The DML changes that you make to the table in the post-upgrade edition are written only to new columns or new tables, never to columns that users of pre-upgrade (ancestor) editions might be reading or writing. However, if the user of an ancestor edition changes the table data, the editioning view that you see must accurately reflect these changes. This is accomplished with forward crossedition triggers.

A forward crossedition trigger defines a transform, which is a rule for transforming an old row to one or more new rows. An old row is a row of data in the pre-upgrade representation. A new row is a row of data in the post-upgrade representation. The name of the trigger refers to the trigger itself and to the transform that the trigger defines.

Reverse Crossedition Triggers

If the pre- and post-upgrade editions will be in ordinary use at the same time (hot rollover), use reverse crossedition triggers to ensure that when users of the post-upgrade edition make changes to the table data, the changes are accurately reflected in the pre-upgrade editions.

Crossedition Trigger Interaction with Editions

The most important difference between crossedition triggers and noncrossedition triggers is how they interact with editions.

In this topic, the current edition is the edition in which the triggering DML statement runs. The current edition might differ from the session edition (for details, see "When the Current Edition Might Differ from the Session Edition").

Which Triggers Are Visible

Editions inherit noncrossedition triggers in the same way that they inherit other editioned objects (see "Inherited and Actual Objects").

Editions do not inherit crossedition triggers. A crossedition trigger might fire in response to a DML statement that another edition runs, but its name is visible only in the edition in which it was created. Therefore, an edition can reuse the name of a crossedition trigger created in an ancestor edition. Reusing the name of a crossedition trigger does not change the conditions under which the older trigger fires.

Crossedition triggers that appear in static data dictionary views are actual objects in the current edition.

What Kind of Triggers Can Fire

What kind of triggers can fire depends on the category of the triggering DML statement. The categories are:

Note:

The APPEND hint on a SQL INSERT statement does not prevent crossedition triggers from firing. For information about the APPEND hint, see Oracle Database SQL Language Reference.
Forward Crossedition Trigger SQL

Forward crossedition trigger SQL is SQL that is executed in either of these ways:

  • Directly from the body of a forward crossedition trigger

    This category includes SQL in an invoked subprogram only if the subprogram is local to the forward crossedition trigger.

  • By invoking the DBMS_SQL.PARSE procedure with a non-NULL value for the apply_crossedition_trigger parameter

    The only valid non-NULL value for the apply_crossedition_trigger parameter is the unqualified name of a forward crossedition trigger. For more information about the DBMS_SQL.PARSE procedure, see Oracle Database PL/SQL Packages and Types Reference.

If a forward crossedition trigger invokes a subprogram in another compilation unit, the SQL in the subprogram is forward crossedition trigger SQL only if it is invoked by the DBMS_SQL.PARSE procedure with a non-NULL value for the apply_crossedition_trigger parameter.

Forward crossedition trigger SQL can fire only triggers that satisfy all of these conditions:

  • They are forward crossedition triggers.

  • They were created either in the current edition or in a descendent of the current edition.

  • They explicitly follow the running forward crossedition trigger.

Reverse Crossedition Trigger SQL

Reverse crossedition trigger SQL is SQL that is executed directly from the body of a reverse crossedition trigger. This category includes SQL in an invoked subprogram only if the subprogram is local to the reverse crossedition trigger.

Reverse crossedition trigger SQL can fire only triggers that satisfy all of these conditions:

  • They are reverse crossedition triggers.

  • They were created either in the current edition or in an ancestor of the current edition.

  • They explicitly precede the running reverse crossedition trigger.

Application SQL

Application SQL is all SQL except crossedition trigger SQL, including these DML statements:

  • Dynamic SQL DML statements coded with the DBMS_SQL package (for information about these statements, see Oracle Database PL/SQL Language Reference).

  • DML statements executed by Java stored procedures and external procedures (even when these procedures are invoked by CALL triggers)

Application SQL fires both noncrossedition and crossedition triggers, according to these rules:

Kind of Trigger Conditions Under Which Trigger Can Fire
Noncrossedition Trigger is both visible and enabled in the current edition.
Forward crossedition Trigger was created in a descendent of the current edition.
Reverse crossedition Trigger was created either in the current edition or in an ancestor of the current edition.

Firing Order

For a trigger to fire in response to a specific DML statement, the trigger must:

  • Be the right kind (see "What Kind of Triggers Can Fire")

  • Satisfy the selection criteria (for example, the type of DML statement and the WHEN clause)

  • Be enabled

For the triggers that meet these requirements, firing order depends on:

See Also:

Oracle Database PL/SQL Language Reference for general information about trigger firing order
FOLLOWS and PRECEDES Clauses

When triggers A and B are to be fired at the same timing point, A fires before B fires if either of these is true:

  • A explicitly precedes B.

  • B explicitly follows A.

This rule is independent of conditions such as:

  • Whether the triggers are enabled or disabled

  • Whether the columns specified in the UPDATE OF clause are modified

  • Whether the WHEN clauses are satisfied

  • Whether the triggers are associated with the same kinds of DML statements (INSERT, UPDATE, or DELETE)

  • Whether the triggers have overlapping timing points

The firing order of triggers that do not explicitly follow or precede each other is unpredictable.

Trigger Type and Edition

For each timing point associated with a triggering DML statement, eligible triggers fire in this order. In categories 1 through 3, FOLLOWS relationships apply; in categories 4 and 5, PRECEDES relationships apply.

  1. Noncrossedition triggers

  2. Forward crossedition triggers created in the current edition

  3. Forward crossedition triggers created in descendents of the current edition, in the order that the descendents were created (child, grandchild, and so on)

  4. Reverse crossedition triggers created in the current edition

  5. Reverse crossedition triggers created in the ancestors of the current edition, in the reverse order that the ancestors were created (parent, grandparent, and so on)

Crossedition Trigger Execution

A crossedition trigger runs using the edition in which it was created. Any code that the crossedition trigger calls (including package references, PL/SQL subprogram calls, and SQL statements) also runs in the edition in which the crossedition trigger was created.

If a PL/SQL package is actual in multiple editions, then the package variables and other state are private in each edition, even within a single session. Because each crossedition trigger and the code that it calls run using the edition in which the crossedition trigger was created, the same session can instantiate two or more versions of the package, with the same name.

Creating a Crossedition Trigger

To create a crossedition trigger, you must be editions-enabled (for information about enabling editions for a user, see "Enabling Editions for a User").

Create a crossedition trigger with the SQL statement CREATE TRIGGER, observing these rules:

  • A crossedition trigger must be defined on a table, not a view.

  • A crossedition trigger must be a DML trigger (simple or compound).

    The DML statement in a crossedition trigger body can be either a static SQL statement (described in Oracle Database PL/SQL Language Reference) or a native dynamic SQL statement (described in Oracle Database PL/SQL Language Reference).

  • A crossedition trigger is forward unless you specify REVERSE. (Specifying FORWARD is optional.)

  • The FOLLOWS clause is allowed only when creating a forward crossedition trigger or a noncrossedition trigger. (The FOLLOWS clause indicates that the trigger being created is to fire after the specified triggers fire.)

  • The PRECEDES clause is allowed only when creating a reverse crossedition trigger. (The PRECEDES clause indicates that the trigger being created is to fire before the specified triggers fire.)

  • The triggers specified in the FOLLOWS or PRECEDES clause must exist, but need not be enabled or successfully compiled.

  • Like a noncrossedition trigger, a crossedition trigger is created in the enabled state unless you specify DISABLE. (Specifying ENABLE is optional.)

    Tip:

    Create crossedition triggers in the disabled state, and enable them after you are sure that they compile successfully. If you create them in the enabled state, and they fail to compile, the failure affects users of the existing application.
  • The operation in a crossedition trigger body must be idempotent (that is, performing the operation multiple times is redundant; it does not change the result).

See Also:

Oracle Database PL/SQL Language Reference for more information about using the CREATE TRIGGER statement to create crossedition triggers

Coding the Forward Crossedition Trigger Body

The operation in the body of a forward crossedition trigger must be idempotent, because it is impossible to predict:

  • The context in which the body will first run for an old row.

    The possibilities are:

  • How many times the body will run for each old row.

Topics:

Handling Data Transformation Collisions

If a forward crossedition trigger populates a new table (rather than new columns of a table), its body must handle data transformation collisions.

For example, suppose that a column of the new table has a UNIQUE constraint. A serendipitous change fires the forward crossedition trigger, which inserts a row in the new table. Later, another serendipitous change fires the forward crossedition trigger, or you apply the transform defined by the trigger. The trigger tries to insert a row in the new table, violating the UNIQUE constraint.

If your collision-handling strategy depends on why the trigger is running, you can determine the reason with the function APPLYING_CROSSEDITION_TRIGGER. When called directly from a trigger body, this function returns the BOOLEAN value TRUE if the trigger is running because of a serendipitous change and FALSE if the trigger is running because you are applying the transform. (APPLYING_CROSSEDITION_TRIGGER is defined in the package DBMS_STANDARD. It has no parameters.)

To ignore collisions and insert the rows that do not collide with existing rows, put the IGNORE_ROW_ON_DUPKEY_INDEX hint in the INSERT statement.

If you do not want to ignore such collisions, but want to know where they occur so that you can handle them, put the CHANGE_DUPKEY_ERROR_INDEX hint in the INSERT or UPDATE statement, specifying either an index or set of columns. Then, when a unique key violation occurs for that index or set of columns, ORA-38911 is reported instead of ORA-00001. You can write an exception handler for ORA-38911.

Note:

Although they have the syntax of hints, IGNORE_ROW_ON_DUPKEY_INDEX and CHANGE_DUPKEY_ERROR_INDEX are mandates. The optimizer always uses them.

Example 19-5 creates a crossedition trigger that uses the APPLYING_CROSSEDITION_TRIGGER function and the IGNORE_ROW_ON_DUPKEY_INDEX and CHANGE_DUPKEY_ERROR_INDEX hints to handle data transformation collisions. The trigger transforms old rows in table1 to new rows in table2. The tables were created as follows:

CREATE TABLE table1 (key NUMBER, value VARCHAR2(20));

CREATE TABLE table2 (key NUMBER, value VARCHAR2(20), last_updated TIMESTAMP);
CREATE UNIQUE INDEX i2 on table2(key);

Example 19-5 Crossedition Trigger that Handles Data Transformation Collisions

CREATE OR REPLACE TRIGGER trigger1
  BEFORE INSERT OR UPDATE ON table1
  FOR EACH ROW
  CROSSEDITION
DECLARE
  row_already_present  EXCEPTION;
  PRAGMA EXCEPTION_INIT(row_already_present, -38911);
BEGIN
  IF APPLYING_CROSSEDITION_TRIGGER THEN
    /* Trigger is running because of serendipitous change.
       Insert new row into table2 unless it is already there. */
    INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(table2(key)) */
    INTO table2
    VALUES(:new.key, :new.value, to_date('1900-01-01', 'YYYY-MM-DD'));
  ELSE
    /* Trigger is running because you are applying transform.
       If tranform has not yet inserted new row in table2, insert new row;
       otherwise, update new row. */
    BEGIN
      INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(table2(key)) */
      INTO table2
      VALUES(:new.key, :new.value, SYSTIMESTAMP);
    EXCEPTION WHEN row_already_present THEN
      UPDATE table2
      SET value = :new.value, last_updated = SYSTIMESTAMP
      WHERE key = :new.key;
    END;
  END IF;
END;
/

See Also:

Handling Changes to Other Tables

If the body of a forward crossedition trigger includes explicit SQL statements that change tables other than the one on which the trigger is defined, and if the rows of those tables do not have a one-to-one correspondence with the rows of the table on which the trigger is defined, then the body code must implement a locking mechanism that correctly handles these situations:

  • Two or more users of ancestor editions simultaneously issue DML statements for the table on which the trigger is defined.

  • At least one user of an ancestor edition issues a DML statement for the table on which the trigger is defined.

Transforming Data from Pre- to Post-Upgrade Representation

After redefining the database objects that comprise the application that you are upgrading (in the new edition), you must transform the application data from its pre-upgrade representation (in the old edition) to its post-upgrade representation (in the new edition). The rules for this transformation are called transforms, and they are defined by forward crossedition triggers. (For general information about forward crossedition triggers, see "Forward Crossedition Triggers".)

Some old rows might have been transformed to new rows by serendipitous changes; that is, by changes that users of the pre-upgrade application made, which fired forward crossedition triggers. However, any rows that were not transformed by serendipitous changes are still in their pre-upgrade representation. To ensure that all old rows are transformed to new rows, you must apply the transforms that you defined on the tables that store the application data.

There are two ways to apply a transform:

  • Fire the trigger that defines the transform on every row of the table, one row at a time.

  • Instead of firing the trigger, run a SQL statement that does what the trigger would do, but faster, and then fire any triggers that follow that trigger.

    This second way is recommended if you have replaced an entire table or created a new table.

For either way of applying the transform, invoke either the DBMS_SQL.PARSE procedure or the subprograms in the DBMS_PARALLEL_EXECUTE package. The latter is recommended if you have a lot of data. The subprograms enable you to incrementally update the data in a large table in parallel, in two high-level steps:

  1. Group sets of rows in the table into smaller chunks.

  2. Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk.

The advantages are:

  • You lock only one set of rows at a time, for a relatively short time, instead of locking the entire table.

  • You do not lose work that has been done if something fails before the entire operation finishes.

For both the DBMS_SQL.PARSE procedure and the DBMS_PARALLEL_EXECUTE subprograms, the actual parameter values for apply_crossedition_trigger, fire_apply_trigger, and sql_stmt are the same:

  • For apply_crossedition_trigger, specify the name of the forward crossedition trigger that defines the transform to be applied.

  • To fire the trigger on every row of the table, one row at a time:

    • For the value of fire_apply_trigger, specify TRUE.

    • For sql_stmt, supply a SQL statement whose only significant effect is to select the forward crossedition trigger to be fired; for example, an UPDATE statement that sets some column to its own existing value in each row.

  • To run a SQL statement that does what the trigger would do, and then fire any triggers that follow that trigger:

    • For the value of fire_apply_trigger, specify FALSE.

    • For sql_stmt, supply a SQL statement that does what the forward crossedition trigger would do, but faster—for example, a PL/SQL anonymous block that calls one or more PL/SQL subprograms.

See Also:

Preventing Lost Updates

To prevent lost updates when applying a transform, use this procedure:

  1. Enable crossedition triggers.

  2. Wait until pending changes to the affected tables are either committed or rolled back.

    Use the procedure DBMS_UTILITY.WAIT_ON_PENDING_DML, described in Oracle Database PL/SQL Packages and Types Reference.

  3. Apply the transform.

Note:

This scenario, where the forward crossedition trigger changes only the table on which it is defined, is sufficient to illustrate the risk. Suppose that Session One issues an UPDATE statement against the table when the crossedition trigger is not yet enabled; and that Session Two then enables the crossedition trigger and immediately applies the transformation.

A race condition can now occur when both Session One and Session Two will change the same row (row n). Chance determines which session reaches row n first. Both updates succeed, even if the session that reaches row n second must wait until the session that reached it first commits its change and releases its lock.

The problem occurs when Session Two wins the race. Because its SQL statement was compiled after the trigger was enabled, the program that implements the statement also implements the trigger action; therefore, the intended post-upgrade column values are set for row n. Now Session One reaches row n, and because its SQL statement was compiled before the trigger was enabled, the program that implements the statement does not implement the trigger action. Therefore, the values that Session Two set in the post-upgrade columns do not change—they reflect the values that the source columns had before Session One updated row n. That is, the intended side-effect of Session One's update is lost.

Dropping the Crossedition Triggers

To drop a crossedition trigger, use the DROP TRIGGER statement, described in Oracle Database PL/SQL Language Reference. Alternatively, you can drop crossedition triggers by dropping the edition in which they are actual, by using the DROP EDITION statement with the CASCADE clause. For information about dropping editions, see "Dropping an Edition".

You drop crossedition triggers in these situations:

  • You are rolling back the application upgrade (dropping the post-upgrade edition).

    Before dropping the post-upgrade edition, you must disable or drop any constraints on the new columns.

  • You have finished the application upgrade and made the post-upgrade edition available to all users.

    When all sessions are using the post-upgrade edition, you can drop the forward crossedition triggers. However, before dropping the reverse crossedition triggers, you must disable or drop any constraints on the old columns.

To disable or drop constraints, use the ALTER TABLE statement with the DISABLE CONSTRAINT or DROP CONSTRAINT clause. For information about the ALTER TABLE statement, see Oracle Database SQL Language Reference.

Displaying Information About Editions, Editioning Views, and Crossedition Triggers

Table 19-1 and Table 19-2 describe the static data dictionary views that display information about editions and editioning views, respectively. For more information about a view in either table, see Oracle Database Reference.

The static data dictionary views that display information about triggers are described in Oracle Database Reference. Crossedition triggers that appear in static data dictionary views are actual objects in the session edition.

Child cursors cannot be shared if the set of crossedition triggers that might run differs. The dynamic performance views V$SQL_SHARED_CURSOR and GV$SQL_SHARED_CURSOR have a CROSSEDITION_TRIGGER_MISMATCH column that tells whether this is true. For information about V$SQL_SHARED_CURSOR, see Oracle Database Reference.

Table 19-1 *_ Dictionary Views with Edition Information

View Description

*_EDITIONS

Describe every edition in the database.

*_EDITION_COMMENTS

Show the comments associated with every edition in the database.

*_OBJECTS

Describe every object in the database that is visible (actual or inherited) in the session edition.

*_OBJECTS_AE

Describe every actual object in the database, in every edition.

*_ERRORS

Describe every error in the database in the session edition.

*_ERRORS_AE

Describe every error in the database, in every edition.

*_USERS

Describe every user in the database. Useful for showing which users have editions enabled.

*_SERVICES

Describe every service in the database.

As of Oracle Database 11g Release 2 (11.2.0.2), the EDITIONS column shows the default initial session edition.


Note:

*_OBJECTS and *_OBJECTS_AE include dependent objects that are invalidated by operations in Table 18-2 only after one of the following:

Table 19-2 *_ Dictionary Views with Editioning View Information

View Description

*_VIEWS

Describes every view in the database that is visible (actual or inherited) in the session edition, including editioning views.

*_EDITIONING_VIEWS

Describes every editioning view in the database that is visible (actual or inherited) in the session edition. Useful for showing relationships between editioning views and their base tables. Join with *_OBJECTS_AE for additional information.

*_EDITIONING_VIEWS_AE

Describes every actual object in every editioning view in the database, in every edition.

*_EDITIONING_VIEW_COLS

Describes the columns of every editioning view in the database that is visible (actual or inherited) in the session edition. Useful for showing relationships between the columns of editioning views and the table columns to which they map. Join with *_OBJECTS_AE, *_TAB_COL, or both, for additional information.

*_EDITIONING_VIEW_COLS_AE

Describes the columns of every editioning view in the database, in every edition.


Each row of *_EDITIONING_VIEWS matches exactly one row of *_VIEWS, and each row of *_VIEWS that has EDITIONING_VIEW = 'Y' matches exactly one row of *_EDITIONING_VIEWS. Therefore, in this example, the WHERE clause is redundant:

SELECT ...
  FROM DBA_EDITIONING_VIEWS INNER JOIN DBA_VIEWS
  USING (OWNER, VIEW_NAME)
  WHERE EDITIONING_VIEW = 'Y'
  AND ...

The row of *_VIEWS that matches a row of *_EDITIONING_VIEWS has EDITIONING_VIEW = 'Y' by definition. Conversely, no row of *_VIEWS that has EDITIONING_VIEW = 'N' has a counterpart in *_ EDITIONING_VIEWS.

Using Edition-Based Redefinition to Upgrade an Application

To use edition-based redefinition to upgrade your application online, you must first ready your application:

  1. Editions-enable the appropriate users.

    For instructions, see "Enabling Editions for a User".

    The reason for this step is that only editions-enabled users can own editioning views, which you create in the next step.

  2. Prepare your application to use editioning views.

    For instructions, see "Preparing Your Application to Use Editioning Views".

With the editioning views in place, you can use edition-based redefinition to upgrade your application online as often as necessary. For each upgrade:

Topics:

Preparing Your Application to Use Editioning Views

An application that uses one or more tables must cover each table with an editioning view. An editioning view covers a table when all of these statements are true:

  • Every ordinary object in the application references the table only through the editioning view. (An ordinary object is any object except an editioning view or crossedition trigger. Editioning views and crossedition triggers must reference tables.)

  • Application users are granted object privileges only on the editioning view, not on the table.

  • Oracle Virtual Private Database (VPD) policies are attached only to the editioning view, not to the table. (Regular auditing and fine-grained auditing (FGA) policies are attached only to the table.)

When the editioning view is actualized, a copy of the VPD policy is attached to the actualized editioning view. (A policy is uniquely identified by its name and the object to which is it attached.) If the policy function is also actualized, the copy of the policy uses the actualized policy function; otherwise, it uses the original policy function.

The static data dictionary views *_POLICIES, which describe the VPD policies, can have different results in different editions.

See Also:

If an existing application does not use editioning views, prepare it to use them by following this procedure for each table that it uses:

  1. Give the table a new name (so that you can give its current name to its editioning view).

    Oracle recommends choosing a new name that is related to the original name and reflects the change history. For example, if the original table name is Data, the new table name might be Data_1.

  2. (Optional) Give each column of the table a new name.

    Again, Oracle recommends choosing new names that are related to the original names and reflect the change history. For example, Name and Number might be changed to Name_1 and Number_1.

    Any triggers that depend on renamed columns are now invalid. For details, see the entry for ALTER TABLE table RENAME column in Table 18-2.

  3. Create the editioning view, giving it the original name of the table.

    For instructions, see "Creating an Editioning View".

    Because the editioning view has the name that the table had, objects that reference that name now reference the editioning view.

  4. If triggers are defined on the table, drop them, and rerun the code that created them.

    Now the triggers that were defined on the table are defined on the editioning view.

  5. If VPD policies are attached to the table, drop the policies and policy functions and rerun the code that created them.

    Now the VPD policies that were attached to the table are attached to the editioning view.

  6. Revoke all object privileges on the table from all application users.

    To see which application users have which object privileges on the table, use this query:

    SELECT GRANTEE, PRIVILEGE
    FROM DBA_TAB_PRIVS
    WHERE TABLE_NAME='table_name';
    
  7. For every privilege revoked in step 6, grant the same privilege on the editioning view.

  8. Enable editions for users who own private synonyms that refer to the table (for instructions, see "Enabling Editions for a User") and notify those users that they must re-create those synonyms.

Note:

Public synonyms that refer to the table now fail with ORA-00980, and you cannot re-create them on the editioning view (for the reason, see "Actualizing Referenced Objects").

Procedure for Edition-Based Redefinition Using Only Editions

Use this procedure only if the type of every object that you will redefine is editionable (as defined in "Editionable and Noneditionable Schema Object Types"). Table is not an editionable type.

  1. Create a new edition.

    For instructions, see "Creating an Edition".

  2. Make the new edition your session edition.

    For instructions, see "Changing Your Session Edition".

  3. Make the necessary changes to the editioned objects of the application.

  4. Ensure that all objects are valid.

    Query the static data dictionary *_OBJECTS_AE, which describes every actual object in the database, in every edition. If invalid objects remain, recompile them, using any UTL_RECOMP subprogram (described in Oracle Database PL/SQL Packages and Types Reference).

  5. Check that the changes work as intended.

    If so, go to step 6.

    If not, either make further changes (return to step 3) or roll back the application upgrade (for instructions, see "Rolling Back the Application Upgrade").

  6. Make the new edition (the upgraded application) available to all users.

    For instructions, see "Making an Edition Available to All Users".

  7. Retire the old edition (the original application), so that all users except SYS use only the upgraded application.

    For instructions, see "Retiring an Edition".

Example 19-6 shows how to use the preceding procedure to change a very simple PL/SQL procedure.

Example 19-6 Edition-Based Redefinition of Very Simple Procedure

  1. Create PL/SQL procedure for this example:

    CREATE OR REPLACE PROCEDURE hello IS
    BEGIN
      DBMS_OUTPUT.PUT_LINE('Hello, edition 1.');
    END hello;
    /
    
  2. Invoke PL/SQL procedure:

    BEGIN hello(); END;
    /
    

    Result:

    Hello, edition 1.
     
    PL/SQL procedure successfully completed.
    
  3. Do edition-based redefinition of procedure:

    1. Create new edition:

      CREATE EDITION e2 AS CHILD OF ora$base;
      

      Result:

      Edition created.
      
    2. Make new edition your session edition:

      ALTER SESSION SET EDITION = e2;
      

      Result:

      Session altered.
      
    3. Change procedure:

      CREATE OR REPLACE PROCEDURE hello IS
      BEGIN
        DBMS_OUTPUT.PUT_LINE('Hello, edition 2.');
      END hello;
      /
      

      Result:

      Procedure created.
      
    4. Check that change works as intended:

      BEGIN hello(); END;
      /
      

      Result:

      Hello, edition 2.
      PL/SQL procedure successfully completed.
      
    5. Make new edition available to all users (requires system privileges):

      ALTER DATABASE DEFAULT EDITION = e2;
      
    6. Retire old edition (requires system privileges):

      List grantees:

      SELECT GRANTEE, PRIVILEGE
      FROM DBA_TAB_PRIVS
      WHERE TABLE_NAME = UPPER('ora$base')
      /
      

      Result:

      GRANTEE                        PRIVILEGE
      ------------------------------ ---------
      PUBLIC                         USE
       
      1 row selected.
      

      Revoke use on old edition from all grantees:

      REVOKE USE ON EDITION ora$base FROM PUBLIC;
      

Procedure for Edition-Based Redefinition Using Editioning Views

Use this procedure only if you will change the structure of one or more tables, and while you are doing so, other users do not need to be able to change data in those tables.

  1. Create a new edition.

    For instructions, see "Creating an Edition".

  2. Make the new edition your session edition.

    For instructions, see "Changing Your Session Edition".

  3. In the new edition, if the editioning views are read-only, make them read-write.

    For instructions, see "Changing the 'Write-ability' of an Editioning View".

  4. In every edition except the new edition, make the editioning views read-only.

  5. Make the necessary changes to the objects of the application.

  6. Ensure that all objects are valid.

    Query the static data dictionary *_OBJECTS_AE, which describes every actual object in the database, in every edition. If invalid objects remain, recompile them, using any UTL_RECOMP subprogram (described in Oracle Database PL/SQL Packages and Types Reference).

  7. Check that the changes work as intended.

    If so, go to step 8.

    If not, either make further changes (return to step 5) or roll back the application upgrade (for instructions, see "Rolling Back the Application Upgrade").

  8. Make the upgraded application available to all users.

    For instructions, see "Making an Edition Available to All Users".

  9. Retire the old edition (the original application), so that all users except SYS use only the upgraded application.

    For instructions, see "Retiring an Edition".

Procedure for Edition-Based Redefinition Using Crossedition Triggers

Use this procedure only if you will change the structure of one or more tables, and while you are doing so, other users must be able to change data in those tables.

  1. Create a new edition.

    For instructions, see "Creating an Edition".

  2. Make the new edition your session edition.

    For instructions, see "Changing Your Session Edition".

  3. Make the permanent changes to the objects of the application.

    For example, add new columns to the tables and create any new permanent subprograms.

    Objects that depend on objects that you changed might now be invalid. For more information, see Table 18-2.

  4. Ensure that all objects are valid.

    Query the static data dictionary *_OBJECTS_AE, which describes every actual object in the database, in every edition. If invalid objects remain, recompile them, using any UTL_RECOMP subprogram (described in Oracle Database PL/SQL Packages and Types Reference).

  5. Create the temporary objects—the crossedition triggers (in the disabled state) and any subprograms that they need.

    For instructions, see "Creating a Crossedition Trigger".

    You need reverse crossedition triggers only if you do step 10, which is optional.

  6. When the crossedition triggers compile successfully, enable them.

    Use the ALTER TRIGGER statement with the ENABLE option. For information about this statement, see Oracle Database PL/SQL Language Reference.

  7. Wait until pending changes are either committed or rolled back.

    Use the procedure DBMS_UTILITY.WAIT_ON_PENDING_DML, described in Oracle Database PL/SQL Packages and Types Reference.

  8. Apply the transforms.

    For instructions, see "Transforming Data from Pre- to Post-Upgrade Representation".

    Note:

    It is impossible to predict whether this step visits an existing row before a user of an ancestor edition updates, inserts, or deletes data from that row.
  9. Check that the changes work as intended.

    If so, go to step 10.

    If not, either make further changes (return to step 3) or roll back the application upgrade (for instructions, see "Rolling Back the Application Upgrade").

  10. (Optional) Grant the USE privilege on your session edition to the early users of the upgraded application.

    For instructions, see "Making an Edition Available to Some Users".

  11. Make the upgraded application available to all users.

    For instructions, see "Making an Edition Available to All Users".

  12. Disable or drop the constraints and then drop the crossedition triggers.

    For instructions, see "Dropping the Crossedition Triggers".

  13. Retire the old edition (the original application), so that all users except SYS use only the upgraded application.

    For instructions, see "Retiring an Edition".

Rolling Back the Application Upgrade

To roll back the application upgrade:

  1. Change your session edition to something other than the new edition that you created for the upgrade.

    For instructions, see "Changing Your Session Edition".

  2. Drop the new edition that you created for the upgrade.

    For instructions, see "Dropping an Edition".

  3. If you created new table columns during the upgrade, reclaim the space that they occupy (for instructions, see "Reclaiming Space Occupied by Unused Table Columns").

Reclaiming Space Occupied by Unused Table Columns

If you roll back an upgrade for which you created new table columns,

To reclaim the space that unused columns occupy:

  1. Set the values of the unused columns to NULL.

    To avoid locking out other users while doing this operation, use the DBMS_PARALLEL_EXECUTE procedure (described in Oracle Database PL/SQL Packages and Types Reference).

  2. Set the unused columns to UNUSED.

    Use the ALTER TABLE statement (described in Oracle Database SQL Language Reference) with the SET UNUSED clause (described in Oracle Database SQL Language Reference).

  3. Shrink the table.

    Use the ALTER TABLE statement (described in Oracle Database SQL Language Reference) with the SHRINK SPACE clause (described in Oracle Database SQL Language Reference).

Example: Using Edition-Based Redefinition to Upgrade an Application

This example uses an edition, an editioning view, a forward crossedition trigger, and a reverse crossedition trigger.

Topics:

Note:

Before you can use edition-based redefinition to upgrade an application, you must enable editions for every schema that the application uses. For instructions, see "Enabling Editions for a User".

Existing Application

The existing application—the application to be upgraded—consists of a single table on which a trigger is defined. The application was created as in Example 19-7.

Example 19-7 Creating the Existing Application

  1. Create table:

    CREATE TABLE Contacts(
      ID            NUMBER(6,0) CONSTRAINT Contacts_PK PRIMARY KEY,
      Name          VARCHAR2(47),
      Phone_Number  VARCHAR2(20)
    );
    
  2. Populate table (not shown).

  3. Prepare to create trigger on table:

    ALTER TABLE Contacts ENABLE VALIDATE CONSTRAINT Contacts_PK;
     
    DECLARE Max_ID INTEGER;
    BEGIN
      SELECT MAX(ID) INTO Max_ID FROM Contacts;
      EXECUTE IMMEDIATE '
        CREATE SEQUENCE Contacts_Seq
          START WITH '||To_Char(Max_ID + 1);
    END;
    /
    
  4. Create trigger:

    CREATE TRIGGER Contacts_BI
      BEFORE INSERT ON Contacts FOR EACH ROW
    BEGIN
      :NEW.ID := Contacts_Seq.NEXTVAL;
    END;
    /
    

Example 19-8 shows how the table Contacts looks after being populated with data.

Example 19-8 Viewing Data in Existing Table

Query:

SELECT * FROM Contacts
ORDER BY Name;
 

Result:

        ID NAME                                            PHONE_NUMBER
---------- ----------------------------------------------- --------------------
       174 Abel, Ellen                                     011.44.1644.429267
       166 Ande, Sundar                                    011.44.1346.629268
       130 Atkinson, Mozhe                                 650.124.6234
       105 Austin, David                                   590.423.4569
       204 Baer, Hermann                                   515.123.8888
       116 Baida, Shelli                                   515.127.4563
       167 Banda, Amit                                     011.44.1346.729268
       172 Bates, Elizabeth                                011.44.1343.529268
       192 Bell, Sarah                                     650.501.1876
       151 Bernstein, David                                011.44.1344.345268
       129 Bissot, Laura                                   650.124.5234
       169 Bloom, Harrison                                 011.44.1343.829268
       185 Bull, Alexis                                    650.509.2876
       187 Cabrio, Anthony                                 650.509.4876
       148 Cambrault, Gerald                               011.44.1344.619268
       154 Cambrault, Nanette                              011.44.1344.987668
       110 Chen, John                                      515.124.4269
       ...
       120 Weiss, Matthew                                  650.123.1234
       200 Whalen, Jennifer                                515.123.4444
       149 Zlotkey, Eleni                                  011.44.1344.429018

107 rows selected.

Suppose that you must redefine Contacts, replacing the Name column with the columns First_Name and Last_Name, and adding the column Country_Code. Also suppose that while you are making this structural change, other users must be able to change the data in Contacts.

You need all features of edition-based redefinition: the edition, which is always needed; the editioning view, because you are redefining a table; and crossedition triggers, because other users must be able to change data in the table while you are redefining it.

Preparing the Application to Use Editioning Views

Example 19-9 shows how to create the editioning view from which other users will access the table Contacts while you are redefining it in the new edition.

Example 19-9 Creating an Editioning View for the Existing Table

  1. Give table a new name (so that you can give its current name to editioning view):

    ALTER TABLE Contacts RENAME TO Contacts_Table;
    
  2. (Optional) Give columns of table new names:

    ALTER TABLE Contacts_Table
      RENAME COLUMN Name TO Name_1;
    
    ALTER TABLE Contacts_Table
      RENAME COLUMN Phone_Number TO Phone_Number_1;
    
  3. Create editioning view:

    CREATE OR REPLACE EDITIONING VIEW Contacts AS
      SELECT
        ID                 ID,
        Name_1             Name,
        Phone_Number_1     Phone_Number
      FROM Contacts_Table;
    
  4. Move trigger Contacts_BI from table to editioning view:

    DROP TRIGGER Contacts_BI;
     
    CREATE TRIGGER Contacts_BI
      BEFORE INSERT ON Contacts FOR EACH ROW
    BEGIN
      :NEW.ID := Contacts_Seq.NEXTVAL;
    END;
    /
    

Using Edition-Based Redefinition to Upgrade the Application

Example 19-10 shows how to create an edition in which to upgrade the "Existing Application", make the new edition the session edition, and check that the new edition really is the session edition.

Example 19-10 Creating Edition in Which to Upgrade the Application

  1. Create new edition:

    CREATE EDITION Post_Upgrade AS CHILD OF Ora$Base;
    
  2. Make new edition your session edition:

    ALTER SESSION SET EDITION = Post_Upgrade;
    
  3. Check session edition:

    SELECT
    SYS_CONTEXT('Userenv', 'Current_Edition_Name') "Current_Edition"
    FROM DUAL;
    

    Result:

    Current_Edition
    -----------------------------------------------------------------------------
    POST_UPGRADE
     
    1 row selected.
    

In the Post_Upgrade edition, Example 19-11 shows how to add the new columns to the physical table and recompile the trigger that was invalidated by adding the columns. Then, it shows how to replace the editioning view Contacts so that it selects the columns of the table by their desired logical names.

Example 19-11 Changing the Table and Replacing the Editioning View

  1. Add new columns to physical table:

    ALTER TABLE Contacts_Table ADD (
      First_Name_2     varchar2(20),
      Last_Name_2      varchar2(25),
      Country_Code_2   varchar2(20),
      Phone_Number_2   varchar2(20)
    );
    

    (This is nonblocking DDL.)

  2. Recompile invalidated trigger:

    ALTER TRIGGER Contacts_BI COMPILE REUSE SETTINGS;
    
  3. Replace editioning view so that it selects replacement columns with their desired logical names:

    CREATE OR REPLACE EDITIONING VIEW Contacts AS
      SELECT
        ID                 ID,
        First_Name_2       First_Name,
        Last_Name_2        Last_Name,
        Country_Code_2     Country_Code,
        Phone_Number_2     Phone_Number
      FROM Contacts_Table;
    

In the Post_Upgrade edition, Example 19-12 shows how to create two procedures for the forward crossedition trigger to use, create both the forward and reverse crossedition triggers in the disabled state, and enable them.

Example 19-12 Creating and Enabling the Crossedition Triggers

  1. Create first procedure that forward crossedition trigger uses:

    CREATE OR REPLACE PROCEDURE Set_First_And_Last_Name (
      Name        IN  VARCHAR2,
      First_Name  OUT VARCHAR2,
      Last_Name   OUT VARCHAR2)
    IS
      Comma_Pos NUMBER := INSTR(Name, ',');
    BEGIN
      IF Comma_Pos IS NULL OR Comma_Pos < 2 THEN
        RAISE Program_Error;
      END IF;
     
      Last_Name := SUBSTR(Name, 1, Comma_Pos-1);
      Last_Name := RTRIM(Ltrim(Last_Name));
     
      First_Name := SUBSTR(Name, Comma_Pos+1);
      First_Name := RTRIM(LTRIM(First_Name));
    END Set_First_And_Last_Name;
    /
    
  2. Create second procedure that forward crossedition trigger uses:

    CREATE OR REPLACE PROCEDURE Set_Country_Code_And_Phone_No (
      Phone_Number     IN  VARCHAR2,
      Country_Code     OUT VARCHAR2,
      Phone_Number_V2  OUT VARCHAR2)
    IS
      Char_To_Number_Error EXCEPTION;
      PRAGMA EXCEPTION_INIT(Char_To_Number_Error, -06502);
      Bad_Phone_Number EXCEPTION;
      Nmbr VARCHAR2(30) := REPLACE(Phone_Number, '.', '-');
     
      FUNCTION Is_US_Number(Nmbr IN VARCHAR2)
        RETURN BOOLEAN
      IS
        Len NUMBER := LENGTH(Nmbr);
        Dash_Pos NUMBER := INSTR(Nmbr, '-');
        n PLS_INTEGER;
      BEGIN
        IF Len IS NULL OR Len <> 12 THEN
          RETURN FALSE;
        END IF;
        IF Dash_Pos IS NULL OR Dash_Pos <> 4 THEN
          RETURN FALSE;
        END IF;
        BEGIN
          n := TO_NUMBER(SUBSTR(Nmbr, 1, 3));
        EXCEPTION WHEN Char_To_Number_Error THEN
          RETURN FALSE;
        END;
     
        Dash_Pos := INSTR(Nmbr, '-', 5);
     
        IF Dash_Pos IS NULL OR Dash_Pos <> 8 THEN
          RETURN FALSE;
        END IF;
     
        BEGIN
          n := TO_NUMBER(SUBSTR(Nmbr, 5, 3));
        EXCEPTION WHEN Char_To_Number_Error THEN
          RETURN FALSE;
        END;
     
        BEGIN
          n := TO_NUMBER(SUBSTR(Nmbr, 9));
        EXCEPTION WHEN Char_To_Number_Error THEN
          RETURN FALSE;
        END;
     
        RETURN TRUE;
      END Is_US_Number;
     
    BEGIN
      IF Nmbr LIKE '011-%' THEN
        DECLARE
          Dash_Pos NUMBER := INSTR(Nmbr, '-', 5);
        BEGIN
          Country_Code := '+'|| TO_NUMBER(SUBSTR(Nmbr, 5, Dash_Pos-5));
          Phone_Number_V2 := SUBSTR(Nmbr, Dash_Pos+1);
        EXCEPTION WHEN Char_To_Number_Error THEN
          raise Bad_Phone_Number;
        END;
      ELSIF Is_US_Number(Nmbr) THEN
        Country_Code := '+1';
        Phone_Number_V2 := Nmbr;
      ELSE
        RAISE Bad_Phone_Number;
      END IF;
    EXCEPTION WHEN Bad_Phone_Number THEN
      Country_Code := '+0';
      Phone_Number_V2 := '000-000-0000';
    END Set_Country_Code_And_Phone_No;
    /
    
  3. Create forward crossedition trigger in disabled state:

    CREATE OR REPLACE TRIGGER Contacts_Fwd_Xed
      BEFORE INSERT OR UPDATE ON Contacts_Table
      FOR EACH ROW
      FORWARD CROSSEDITION
      DISABLE
    BEGIN
      Set_First_And_Last_Name(
        :NEW.Name_1,
        :NEW.First_Name_2,
        :NEW.Last_Name_2
      );
      Set_Country_Code_And_Phone_No(
        :NEW.Phone_Number_1,
        :NEW.Country_Code_2,
        :NEW.Phone_Number_2
      );
    END Contacts_Fwd_Xed;
    /
    
  4. Enable forward crossedition trigger:

    ALTER TRIGGER Contacts_Fwd_Xed ENABLE;
    
  5. Create reverse crossedition trigger in disabled state:

    CREATE OR REPLACE TRIGGER Contacts_Rvrs_Xed
      BEFORE INSERT OR UPDATE ON Contacts_Table
      FOR EACH ROW
      REVERSE CROSSEDITION
      DISABLE
    BEGIN
      :NEW.Name_1 := :NEW.Last_Name_2||', '||:NEW.First_Name_2;
      :NEW.Phone_Number_1 :=
      CASE :New.Country_Code_2
        WHEN '+1' THEN
          REPLACE(:NEW.Phone_Number_2, '-', '.')
        ELSE
          '011.'||LTRIM(:NEW.Country_Code_2, '+')||'.'||
          REPLACE(:NEW.Phone_Number_2, '-', '.')
      END;
    END Contacts_Rvrs_Xed;
    /
    
  6. Enable reverse crossedition trigger:

    ALTER TRIGGER Contacts_Rvrs_Xed ENABLE;
    
  7. Wait until pending changes are either committed or rolled back:

    DECLARE
      scn              NUMBER  := NULL;
      timeout CONSTANT INTEGER := NULL;
    BEGIN
      IF NOT DBMS_UTILITY.WAIT_ON_PENDING_DML(Tables  => 'Contacts_Table',
                                              timeout => timeout,
                                              scn     => scn)
      THEN
        RAISE_APPLICATION_ERROR(-20000,
         'Wait_On_Pending_DML() timed out. CETs were enabled before SCN: '||SCN);
      END IF;
    END;
    /
    

    For information about the DBMS_UTILITY.WAIT_ON_PENDING_DML procedure, see Oracle Database PL/SQL Packages and Types Reference.

In the Post_Upgrade edition, Example 19-13 shows how to apply the transforms.

Example 19-13 Applying the Transforms

DECLARE
  c NUMBER := DBMS_SQL.OPEN_CURSOR();
  x NUMBER;
BEGIN
  DBMS_SQL.PARSE(
    c                          => c,
    Language_Flag              => DBMS_SQL.NATIVE,
    Statement                  => 'UPDATE Contacts_Table SET ID = ID',
    Apply_Crossedition_Trigger => 'Contacts_Fwd_Xed'
  );
  x := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.CLOSE_CURSOR(c);
  COMMIT;
END;
/
 

In the Post_Upgrade edition, Example 19-14 shows how to check that the change worked as intended. Compare Example 19-14 to Example 19-8.

Example 19-14 Viewing Data in Changed Table

  1. Format columns for readability:

    COLUMN ID FORMAT 999
    COLUMN Last_Name FORMAT A15
    COLUMN First_Name FORMAT A15
    COLUMN Country_Code FORMAT A12
    COLUMN Phone_Number FORMAT A12
    
  2. Query:

    SELECT * FROM Contacts
    ORDER BY Last_Name;
    

    Result:

      ID FIRST_NAME      LAST_NAME       COUNTRY_CODE PHONE_NUMBER
    ---- --------------- --------------- ------------ ------------
     174 Ellen           Abel            +44          1644-429267
     166 Sundar          Ande            +44          1346-629268
     130 Mozhe           Atkinson        +1           650-124-6234
     105 David           Austin          +1           590-423-4569
     204 Hermann         Baer            +1           515-123-8888
     116 Shelli          Baida           +1           515-127-4563
     167 Amit            Banda           +44          1346-729268
     172 Elizabeth       Bates           +44          1343-529268
     192 Sarah           Bell            +1           650-501-1876
     151 David           Bernstein       +44          1344-345268
     129 Laura           Bissot          +1           650-124-5234
     169 Harrison        Bloom           +44          1343-829268
     185 Alexis          Bull            +1           650-509-2876
     187 Anthony         Cabrio          +1           650-509-4876
     154 Nanette         Cambrault       +44          1344-987668
     148 Gerald          Cambrault       +44          1344-619268
     110 John            Chen            +1           515-124-4269
           ...
     120 Matthew         Weiss           +1           650-123-1234
     200 Jennifer        Whalen          +1           515-123-4444
     149 Eleni           Zlotkey         +44          1344-429018
     
    107 rows selected.
    

If the change worked as intended, you can now follow steps 10 through 13 of the "Procedure for Edition-Based Redefinition Using Crossedition Triggers".