In the following statements, the user HR is given the privileges needed to create and use an edition:. The following object types are editionable: Synonym View Function Procedure Package specification and body Type specification and body Library Trigger An editionable object is an object of one of the above editionable object types in an editions-enabled schema.
Note: All database object types not listed above are not editionable. Changes to object types that are not editionable are immediately visible across all editions in the database. Semantics edition Specify the name of the edition to be created. Examples The following very simple examples are intended to show the syntax for creating and working with an edition.
How you specify your initial session edition at connection time depends on how you connect to the database—see the documentation for your interface.
Oracle Database Administrator's Guide for information about setting the database default edition. As of Oracle Database 11 g Release 2 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.
As of Release 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. Oracle Database Administrator's Guide for information about the -t option of the srvctl add service command. Oracle Database Administrator's Guide for information about the -t option of the srvctl modify service command. 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. While the statement is running, the current edition is the specified edition, but the session edition does not change.
Example 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. PARSE procedure. 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.
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:. 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 owner of an editioning view must be editions-enabled before the editioning view is created.
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.
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. 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:. You can replace an editioning view only with another editioning view. Any triggers defined on the replaced editioning view are retained. 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. 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. 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. 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.
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. 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. 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.
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". 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 depends on the category of the triggering DML statement. The categories are:. This category includes SQL in an invoked subprogram only if the subprogram is local to the forward crossedition trigger. This category includes SQL in an invoked subprogram only if the subprogram is local to the reverse crossedition trigger.
For a trigger to fire in response to a specific DML statement, the trigger must:. 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:. The firing order of triggers that do not explicitly follow or precede each other is unpredictable. For each timing point associated with a triggering DML statement, eligible triggers fire in this order. Forward crossedition triggers created in descendents of the current edition, in the order that the descendents were created child, grandchild, and so on.
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. A crossedition trigger runs using the edition in which it was created. 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.
To create a crossedition trigger, you must be editions-enabled for information about enabling editions for a user, see "Enabling Editions for a User". 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.
The operation in the body of a forward crossedition trigger must be idempotent, because it is impossible to predict:. When a user of an ancestor edition runs a DML statement that fires the trigger a serendipitous change. If a forward crossedition trigger populates a new table rather than new columns of a table , its body must handle data transformation collisions. 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. It has no parameters. Then, when a unique key violation occurs for that index or set of columns, ORA is reported instead of ORA You can write an exception handler for ORA The trigger transforms old rows in table1 to new rows in table2.
The tables were created as follows:. 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.
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. 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. 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:. Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk. You lock only one set of rows at a time, for a relatively short time, instead of locking the entire table.
To run a SQL statement that does what the trigger would do, and then fire any triggers that follow that trigger:. 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.
For information about dropping editions, see "Dropping an 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. Table and Table 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. Describe every object in the database that is visible actual or inherited in the session edition. Describes every view in the database that is visible actual or inherited in the session edition, including editioning views. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow.
Learn more. Asked 3 years, 11 months ago. Active 2 years, 9 months ago. Viewed 5k times. Also what does the zd stand for? Improve this question. Superdooperhero Superdooperhero 6, 17 17 gold badges 72 72 silver badges bronze badges. Add a comment. Active Oldest Votes.
0コメント