24 Using Edition-Based Redefinition

24.1.1 Editioned and Noneditioned Objects

An editioned object has both a schema object type that is editionable in its owner and the EDITIONABLE property. An edition has its own copy of an editioned object, and only that copy is visible to the edition.

A noneditioned object has either a schema object type that is noneditionable in its owner or the NONEDITIONABLE property. An edition cannot have its own copy of a noneditioned object. A noneditioned object is visible to all editions.

An object is potentially editioned if enabling editions for its type in its owner would make it an editioned object (for details, see Section 24.1.1.4, "Enabling Editions for a User").

An editioned object belongs to both a schema and an edition, and is uniquely identified by its OBJECT_NAME, OWNER, and EDITION_NAME. A noneditioned object belongs only to a schema, and 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.

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, then the edition is the current edition of the session that issued the command (for information about the current edition, see Section 24.1.6). If the context is source code, then the edition is the one in which the object is actual (see Section 24.1.3).

Topics:

• Name Resolution for Editioned and Noneditioned Objects

• Noneditioned Objects That Can Depend on Editioned Objects

• Editionable and Noneditionable Schema Object Types

• Enabling Editions for a User

• EDITIONABLE and NONEDITIONABLE Properties

• Rules for Editioned Objects

CREATE MATERIALIZED VIEW [ schema.] materialized_view other_clauses
[ evaluation_edition_clause ] [ query_rewrite_clause ] AS subquery

EVALUATE USING { CURRENT EDITION | EDITION edition | NULL EDITION }

{ DISABLE | ENABLE } QUERY REWRITE
[ unusable_before_clause ] [ unusable_beginning_clause ]

UNUSABLE BEFORE { CURRENT EDITION | EDITION edition }

UNUSABLE BEGINNING WITH { CURRENT EDITION | EDITION edition | NULL EDITION }

column [ datatype ] [ GENERATED ALWAYS ] AS ( column_expression )
[ VIRTUAL ] [ evaluation_edition_clause ]
[ unusable_before_clause ] [ unusable_beginning_clause ]
[ inline_constraint ]...

ALTER USER user ENABLE EDITIONS [ FOR type [, type ]... ]

24.1.2 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 24-1 and others use the preceding statement.)

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.

24.1.3 Editioned Objects and Copy-on-Change

When you create an edition, all editioned objects in its parent edition are copied to it. Changes to an editioned object in one edition do not affect copies of that editioned object in other editions.

The preceding paragraph describes what happens conceptually. In practice, to optimize performance, Oracle Database copies an editioned object from an ancestor edition to a descendent edition only when the descendent edition changes the object. This strategy is called copy-on-change.

An editioned object that was conceptually (but not actually) copied to a descendent edition is called an inherited object. When a user of the descendent edition references an inherited object in a DDL statement, Oracle Database actually copies the object to the descendent edition. This copying operation is called actualization, and it creates an actual object in the descendent edition.

Example 24-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 in ora$base. Then e2 changes hello, actualizing it. Now when e2 invokes hello, it invokes its own actual procedure. The procedure hello in the edition ora$base remains unchanged.

Example 24-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.)

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, but it must be an editioned object (for details, see Section 24.1.1.5.1). If e2 creates a new editioned object named goodbye, then the descendents of e2 inherit that object.

In Example 24-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).

24.1.4 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.

24.1.5 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 allowing all users to use the edition, because it effectively grants the USE privilege on edition_name to PUBLIC.

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

24.1.6 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

• Changing Your Session Edition

• Displaying the Names of the Current and Session Editions

• When the Current Edition Might Differ from the Session Edition

SELECT EDITION_NAME FROM ALL_EDITIONS;

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

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

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

24.1.7 Retiring an Edition

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

ALTER DATABASE DEFAULT EDITION = edition_name

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.

When you retire an edition, update the evaluation editions and unusable editions of noneditioned objects accordingly. For information about changing evaluation editions and unused editions, see Section 24.1.1.2, "Noneditioned Objects That Can Depend on Editioned Objects."

24.1.8 Dropping an 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 Section 24.1.6.2.

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.

When you drop an edition, update the evaluation editions and unusable editions of noneditioned objects accordingly. For information about changing evaluation editions and unused editions, see Section 24.1.1.2, "Noneditioned Objects That Can Depend on Editioned Objects."

24.2.1 Creating an Editioning View

Before an editioning view is created, its owner must be editions-enabled and the schema object type VIEW must be editionable in its owner. (For instructions, see Section 24.1.1.4.)

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. Do not specify NONEDITIONABLE, or an error occurs.

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.

24.2.2 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

24.2.3 Changing the Writability 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.

24.2.4 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.

24.2.5 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.

24.2.6 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.

24.2.7 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.

24.3.1 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.

24.3.2 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.

24.3.3 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 Section 24.1.6.4).

Topics:

• Which Triggers Are Visible

• What Kind of Triggers Can Fire

• Firing Order

• Crossedition Trigger Execution

24.3.4 Creating a Crossedition Trigger

Before a crossedition trigger is created, its owner must be editions-enabled and the schema object type TRIGGER must be editionable in its owner. (For instructions, see Section 24.1.1.4.)

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 have the EDITIONABLE property.

• 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).

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);

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;
/

24.3.5 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 Section 24.3.1.)

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 three 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 over the first way if you have replaced an entire table or created a new table.

• Invoke the procedure DBMS_EDITIONS_UTILITIES.SET_NULL_COLUMN_VALUES_TO_EXPR to use a metadata operation to apply the transform to the new column.

  This third way has the fastest installation time, but there are restrictions on the expression that represents the transform, and queries of the new column are slower until the metadata is replaced by actual data.

  Metadata is replaced by actual data:

  • In an individual column element that is updated.

  • In every element of a column whose table is "compacted" using online table redefinition.

  For the syntax, semantics, and restrictions of the DBMS_EDITIONS_UTILITIES.SET_NULL_COLUMN_VALUES_TO_EXPR procedure, see Oracle Database PL/SQL Packages and Types Reference.

For the first two ways 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.

24.3.6 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 Section 24.1.8.

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.

24.4.1 Displaying Information About Editions

Table 24-1 briefly describes the static data dictionary views that display information about editions. For more information about a specific view, see Oracle Database Reference.

24.4.2 Displaying Information About Editioning Views

Table 24-2 briefly describes the static data dictionary views that display information about editioning views. For more information about a specific view, see Oracle Database Reference.

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.

24.4.3 Displaying Information About Crossedition Triggers

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 current 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.

24.5.1 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 it is 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.

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 23-2.

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

   For instructions, see Section 24.2.1.

   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. For each user who owns a private synonym that refers to the table, enable editions, specifying that the type SYNONYM is editionable in the schema (for instructions, see Section 24.1.1.4).

9. Notify the owners of private synonyms that refer to the table that they must re-create those synonyms.

24.5.2 Procedure for EBR Using Only Editions

Use this procedure only if every object that you will redefine is editioned (as defined in Section 24.1.1). Tables are never editioned objects.

1. Create a new edition.

   For instructions, see Section 24.1.2.

2. Make the new edition your session edition.

   For instructions, see Section 24.1.6.2.

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 Section 24.5.5).

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

   For instructions, see Section 24.1.5.

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

   For instructions, see Section 24.1.7.

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

24.5.3 Procedure for EBR 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 need not be able to change data in those tables.

1. Create a new edition.

   For instructions, see Section 24.1.2.

2. Make the new edition your session edition.

   For instructions, see Section 24.1.6.2.

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

   For instructions, see Section 24.2.3.

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 Section 24.5.5).

8. Make the upgraded application available to all users.

   For instructions, see Section 24.1.5.

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

   For instructions, see Section 24.1.7.

24.5.4 Procedure for EBR 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 Section 24.1.2.

2. Make the new edition your session edition.

   For instructions, see Section 24.1.6.2.

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 23-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 Section 24.3.4.

   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 Section 24.3.5.

   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 Section 24.5.5).

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

    For instructions, see Section 24.1.4.

11. Make the upgraded application available to all users.

    For instructions, see Section 24.1.5.

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

    For instructions, see Section 24.3.6.

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

    For instructions, see Section 24.1.7.

24.5.5 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 Section 24.1.6.2.

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

   For instructions, see Section 24.1.8.

3. If you created new table columns during the upgrade, reclaim the space that they occupy (for instructions, see Section 24.5.6).

24.5.6 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).

24.5.7 Example: Using EBR to Upgrade an Application

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

Topics:

• Existing Application

• Preparing the Application to Use Editioning Views

• Using EBR to Upgrade the Application

SELECT * FROM Contacts
ORDER BY Name;
 

        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.

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;
/