3
Using Plan Editor

The Plan Editor application allows you to modify or reproduce (or both) database object definitions at one or more databases.

With Plan Editor, you create or modify a single change plan and populate it with change requests. To deploy the plan, you specify one or more destination databases, generate a script to apply the plan's change requests at each database, then execute the scripts.

You can also create change plans with DB Diff's Synchronization wizard, DB Alter, DB Quick Change, and DB Propagate. However, these applications differ from Plan Editor because each of them is designed to create a change plan with specific types of change requests that make specific types of changes.

Plan Editor is a more flexible change plan tool. You can use Plan Editor to create and modify a change plan that includes any type of change request and to make a wider variety of changes.

This chapter explains in detail the steps for modifying and creating object definitions using Plan Editor.

Modifying and Creating Object Definitions with Plan Editor

This section describes how to start the Plan Editor application and use it to modify and create object definitions.

Note: Plan Editor provides right mouse button support for some operations. After you select an object in the Plan Editor navigator, click the right mouse button to display a menu of options. Any menu options that are not appropriate for the selected object are unavailable from the menu.

Creating a Plan with Plan Editor

To create a new plan using Plan Editor, follow these steps:

1. Start Change Manager from the Oracle Enterprise Manager console. On the Tools menu, point to Change Management Pack, then click Change Manager.

2. On the Change Manager Object menu, click Create Change Plan. In the Create Change Plan Options dialog box, click Manual Creation, which displays the Create Plan dialog box.

3. Create a plan by following these steps, which are also shown in Figure 3-1:
   1. On the General page of the Create Plan dialog box:
      • Supply a unique name for the plan. Oracle Change Management Pack allows plan names and baseline names of up to 50 characters in length. Any character, including blank, is allowed. However, to avoid confusion, it is recommended that you do not use leading or trailing blanks in a plan name or baseline name.

      • Choose the plan's source database (the database used to create the plan's change requests) from the list of databases. The list of databases are the databases known to (discovered by) the Oracle Enterprise Manager console.

      • Provide a description of the plan (optional).

   2. Click Create to create the plan. This launches Plan Editor for the newly-created plan.

Figure 3-1 Creating and Modifying Object Definitions with Plan Editor

Defining Change Requests with Plan Editor

After Plan Editor creates the empty plan, you need to define one or more change requests to add to the plan. Depending on what you want the plan to accomplish, you will define directives, exemplars, or both for the plan.

Understanding Directives

A directive is a set of changes you specify for an existing, named object definition. A directive can be thought of as a "Super Alter" statement.

A directive for an object definition tells an Oracle Change Management Pack application, "Make these specific changes to the object definition." Suppose, for example, that you are defining a directive for a table named Table_1. Some of the change requests that you could specify in a directive for table Table_1 include:

• Adding column REGION_ID with data type of NUMBER(3).

• Renaming the column REGNAME to AREA_NAME.

• Changing the tablespace for the table to tablespace TABLESPACE_1.

You create a directive for an object definition by selecting an object definition to be modified, then specifying the changes to the object definition's attributes on property sheets similar to those in DBA Studio.

A directive has the scope of a single object definition (the changes made by the directive are applied to a single object definition).

It is also possible to extend the scope of a directive so that the changes specified in the directive are applied to multiple object definitions that you specify. A directive with an extended scope is called a scoped directive. Use a scoped directive to make the same set of changes to more than one object definition.

Change plans created with the following applications contain only directives:

• DB Alter (directives and scoped directives can be created)

• DB Quick Change (directives can be created, but scoped directives cannot be created)

Change plans created with Plan Editor can contain directives, scoped directives, and exemplars (or any combination of these types of change requests).

Understanding Exemplars

An exemplar is a complete object definition to be reproduced, either by creating a new object definition or by modifying an existing object definition of the same name and object type. An exemplar can be thought of as the example of what you want to reproduce.

An exemplar tells an Oracle Change Management Pack application, "Reproduce this object definition. If an object definition of the same name and type already exists, make whatever changes are necessary to that object definition so that it matches this object definition. If the object definition does not exist, create an object definition that matches this object definition." When you define an exemplar, you can also include grants information for the exemplar.

Change plans created with the following applications contain only exemplars:

• DB Propagate

• DB Diff's Synchronization wizard

Change plans created with Plan Editor can contain exemplars, directives, and scoped directives (or any combination of these types of change requests).

Table 3-1 summarizes the types of change requests that can be created with Oracle Change Management Pack applications.

Table 3-1 Change Requests Created with Oracle Change Management Pack Applications

Application	Type of Change Requests Created
DB Alter	directives and scoped directives
DB Diff Synchronization wizard	exemplars
DB Propagate	exemplars
DB Quick Change	directives
Plan Editor	directives, scoped directives, and exemplars
DB Search	None

Some Oracle Change Management Pack applications (such as DB Alter and DB Propagate) make it evident when you are creating change requests because these applications prompt you to add directives or exemplars to the change plan. Other applications (such as DB Diff's Synchronization wizard and DB Quick Change) prompt you to select object definitions to copy or modify, but do not make it evident that the selected definitions are directives or exemplars that are being added to the change plan. DB Search does not create change requests.

Defining a Directive with Plan Editor

To specify the modifications to be made to an existing object definition at the destination database, follow these steps to define a directive for that object definition in the source database:

1. On the Plan Editor Plan menu, click New Change Requests.

2. Expand the Source Database tree in the New Change Requests dialog box, then select one or more object definitions for which you want to create directives. Definitions that are already in the plan are shown in gray and cannot be selected.

3. Click the Directive button. Each selected object definition in the Source Database tree turns gray when the directive for the object definition is created. This step also causes the selected directives to be displayed in the Change Plan tree.

4. Click Close or leave the New Change Requests dialog box displayed.

5. In the Plan Editor tree, expand the Change Requests folder under the new plan to display folders for each object type for which change requests have been defined.

6. Expand an object type folder to view the change requests that have been defined for that object type. Directives are marked with both the directive icon and an object type icon. The directive icon appears first, followed by the object type icon, then the name of the object. The directive icon is shown in Figure 3-2.

7. Click the name of a directive in the Plan Editor tree. This displays the General page for the directive in Plan Editor's detail view.

8. On the General page, click the Edit Directive button. This displays the Edit dialog box for the directive. Property sheets for the object are displayed in the Edit dialog box. You use the property pages for the directive to specify the changes that you want to make to the directive object definition.

9. On the property pages, specify the changes you want to make to the object definition. Click OK to accept the changes you have specified.

Figure 3-2 The Directive Icon

Note that a directive describes the changes you want to make to an object definition. Therefore, the meaningful part of a directive is the changes that you have specified. You can view these changes by clicking the directive in the Plan Editor tree and viewing the list of changes on the General page for the object. After you have created a directive for an object, you can make further edits to the directive if the object still exists in the change plan's source database. When you make further edits to a directive, the object's definition is loaded from the source database, and the edits already specified in the directive are applied to the object.

Defining a Scoped Directive with Plan Editor

By default, a directive contains the changes to be applied to a single object definition. However, it is possible to extend the scope of a directive so that the changes specified in the directive are applied to multiple object definitions that you specify. A directive with an extended scope is called a scoped directive.

Scoped directives can be very powerful. For example, suppose that you want to move several tables to a new tablespace without creating a separate directive for each table you want to move. Instead, you can create a directive for one of the tables, and in that directive specify the name of the new tablespace for the table. Then you can edit the scope specification for that directive, making sure that the search criteria identifies the other tables that you want to move to the new tablespace. When the script is generated, the scoped directive's instructions are applied to each object that matches the search criteria. When the script executes, it carries out the scoped directive's instructions on each matched object. In this example, all the tables that match the scope directive's search criteria are moved to the new tablespace that is specified in the scoped directive.

To create a scoped directive, follow these steps:

1. In the Plan Editor tree, click the directive for which you want to edit the scope specification. If you have not already specified the set of changes that you want the directive to apply, click the Edit Directive button on the General page for the directive and specify those changes, as described in "Defining a Directive with Plan Editor".

2. On the General page for the directive, click the Edit Scope button.

3. In the Edit Directive Scope dialog box, specify a set of search criteria to identify the database object definitions to which the changes specified in the scoped directive will be applied.

   The Edit Directive Scope dialog box contains the following fields:

   • Object Type

     Displays the object type of the directive object. This field is non-editable.

   • Schemas

     If the directive object is a non-schema object, you cannot modify this field.

     If the directive object is a schema object, you can modify this field. To include any schema name in the search criteria, click Any Schema. To include any schema name except the SYS and SYSTEM schemas, click Exclude SYS, SYSTEM after clicking Any Schema.

     To include specified schemas or wild card patterns in the search criteria, click Add. The Select Schemas dialog box is displayed. Use the Select Schemas dialog box to add specified schemas or wild card patterns to the search criteria. If you specify wild card patterns, any schema name that matches the pattern will be included in the search criteria.

     To remove specified schemas or wild card patterns from the search criteria, select the item in the Schemas list, then click Remove.

   • Search For

     To include any object name in the search criteria, click Any Object Name.

     To include specified object names or wild card patterns in the search criteria, click Specified Object Names and then click Add. The Select Names dialog box is displayed. Use the Select Names dialog box to add specified object names or wild card patterns to the search criteria. If you specify wild card patterns, any object name that matches the pattern will be included in the search criteria.

     To remove specified schema names or wild card patterns from the search criteria, select the item in the Search For list, then click Remove.

4. Click OK to confirm the search criteria for the scoped directive. A Scope Specification box that includes the search criteria that you chose appears on the General page for the directive. Also, in the Plan Editor tree, the directive icon changes to the scoped directive icon.

Scoped directives are marked with both the scoped directive icon and an object type icon. The scoped directive icon appears first, followed by the object type icon, then the name of the object. The scoped directive icon is shown in Figure 3-3.

Figure 3-3 The Scoped Directive Icon

A change plan can have only one change request for an object in a destination database. For example, a plan cannot contain both a directive and a scoped directive for the same object in a destination database. If a change plan contains multiple change requests for the same object, this problem will be identified in the impact report.

Refer to the online help for more information on scoped directives.

Defining an Exemplar with Plan Editor

To reproduce an existing object definition at a destination database, you create an exemplar for that object definition in the source database. Later, when the script generated from the change plan is executed against the destination database, one of the following three actions is performed for each exemplar in the change plan:

• If an object definition of the same name and type as the exemplar does not exist at the destination database, the script creates the object at the destination database.

• If an object definition of the same name and type as the exemplar exists at the destination database but the object definition is different than the exemplar's definition, the script makes the necessary changes to the object definition so that it matches the exemplar's definition.

• If an object definition of the same name and type as the exemplar exists at the destination database and the object definition exactly matches the exemplar's definition, no changes are made to the object definition at the destination database.

To reproduce an existing object definition at a destination database, follow these steps to create an exemplar for that object definition in the source database:

1. On the Plan Editor Plan menu, click New Change Requests.

2. Expand the Source Database tree in the New Change Requests dialog box, then select one or more object definitions for which you want to create exemplars. Definitions that are already in the plan are shown in gray and cannot be selected.

3. Click the Exemplar button. Each selected object definition in the Source Database tree turns gray when the exemplar for the object definition is created. This step also causes the selected exemplars to be displayed in the Change Plan tree.

4. Click Close or leave the New Change Requests dialog box displayed.

5. In the Plan Editor tree, expand the Change Requests folder under the new plan, which displays folders for each object type for which change requests have been defined.

6. Expand an object type folder to view the change requests that have been defined for that object type. Exemplars are marked with both an object type icon and with the exemplar icon. The exemplar icon appears first, followed by the object type icon, then the name of the object. The exemplar icon is shown in Figure 3-4.

Figure 3-4 The Exemplar Icon

7. To view the attributes for an exemplar, expand the exemplar and click its Attributes subobject. This displays the object definition's property pages in Plan Editor's detail view. You cannot modify the exemplar's attributes.

8. To view the grants associated with an exemplar, expand the exemplar and click its Grants subobject (note that the Grants subobject is not displayed for object types that do not participate in grants). This displays the object definition's Grants property page in Plan Editor's detail view. By default, when you include an exemplar in a plan, all the grants associated with the exemplar object are included in the plan, which means when the object definition is reproduced at a destination database, the object's grants are reproduced, if possible. A grant will be applied if the objects that reference the grant already exist or will be created at the destination database when the change plan's script is executed at the destination database.

   Select one or more of an exemplar's grants and click the Exclude button to exclude those grants from the plan. If you decide later that you want to include one or more excluded grants for an exemplar in the plan, select those grants, then click the Include button to include them in the plan.

   After you include a specific grant for an exemplar in a plan, it is possible for the same grant to be modified in the database. In this case, the Refresh button becomes available when you select that grant on the Grants page. If you want to update the grant in the plan to match the grant in the database, select the grant, then click the Refresh button.

   If no grants are associated with the exemplar, the Grants page does not display any grants.

9. To view the dependencies and dependents of an exemplar, expand the exemplar and click its Dependencies subobject. This displays the Dependencies and Dependents property pages for the exemplar.

   The Dependencies page displays the objects that the exemplar depends on. Each dependency object definition on the Dependencies page should be added to the plan, except for those object definitions that already exist at the destination database. For example, suppose you add an exemplar for a trigger to a plan, and the trigger refers to a table that is not in the plan and which does not exist at the destination database. In this case, you should manually add an exemplar for the referenced table to the plan, otherwise the trigger cannot be created at the destination database. To manually add a dependent object to the plan, select the object definition and click Add to Plan. Objects that are already in the plan are unavailable.

   The Dependents page displays the objects that depend on the exemplar. You can use this page to locate other object definitions that are related to the exemplar, and, if you wish, manually add them to the plan. To manually add a dependency object definition to the plan, select the object definition and click Add to Plan. Objects that are already in the plan are unavailable.

10. You can view and, if desired, change the value of one or more of the plan's propagate options. To do so, click on the plan name in the Plan Editor tree. In the detail view, click the Propagate Options tab to view and, if desired, modify one or more values for the plan's propagate options. The values of the propagate options for a change plan determine how exemplars in the plan are applied when a script generated from the plan is run at a destination database.

    If your plan contains table exemplars, you can reproduce both the table definitions for the exemplars and the data associated with the table definitions at a destination database. To do so, select the Copy Table Data option. If you want to reproduce only the definitions for the table exemplars, do not select the Copy Table Data option.

    See the online help for a complete list of the propagate options and a description of how their values affect the application of a change plan's exemplars at a destination database.

Selecting a Destination Database with Plan Editor

To select the destination database where you want the plan to be executed:

1. On the Plan Editor Plan menu, click New Destination.

2. On the General page of the Create Destination dialog box, select a destination database from the list of available destinations, and, optionally, supply a description for the database.

3. Click Create.

Understanding Script Generation

After a destination database has been selected, a script can be generated from the change plan. The script generated from the change plan (not the plan itself) will be run against the destination database. During the initial stage of script generation, Oracle Change Management Pack examines the structure and definitions in the destination database so that it can generate a script designed exclusively for execution against the destination database.

When you use a single plan to generate scripts for several databases that have different structures and definitions, Oracle Change Management Pack generates a different script for each database. This is because Oracle Change Management Pack takes each destination database's structure and definitions into account when generating the script.

For example, suppose your plan contains an exemplar for table Table_2, and you use Oracle Change Management Pack application to generate two scripts, one to run against destination database DB_1 and the other to run against destination database DB_2. If table Table_2 does not exist in database DB_1, the script generated for DB_1 will include statements that define table Table_2. If a different version of table Table_2 already exists in database DB_2, the script generated for DB_2 will include statements to make the definition of table Table_2 in database DB_2 match the exemplar for table Table_2.

After Oracle Change Management Pack generates a script for a destination database, you can view and, optionally, edit the script. Oracle Change Management Pack also creates an impact report when it generates the script. You should view the impact report to determine the impact of executing the script at the destination database. The impact report provides a summary of the number and types of objects that will be modified when the script executes at the destination database. The impact report also shows warnings and errors, including a description of the requested operations that cannot be performed at the destination database, for example, a request to drop a column that no longer exists at the destination database.

If you modify a plan's change requests after you have generated one or more scripts for the plan, Oracle Change Management Pack considers the scripts that have been generated to be obsolete scripts. When you try to execute an obsolete script, Oracle Change Management Pack displays a message that advises you that the plan was modified after the script was generated and confirms whether you want to execute the script anyway. It is prudent to generate a new script from the modified plan instead of executing an obsolete script.

Generating a Script with Plan Editor

To generate a script for the destination database:

1. Under the Destinations folder, expand the destination database.

2. Click the Script subobject. This displays the script property pages in the detail view.

3. On the Options page, you can map schemas in the source database to their corresponding schemas in the destination database. By default, change requests specified for database objects in a source schema are applied to a destination schema with the same name. You only need to map schemas when you want change requests for the object definitions in a schema in the source database to be applied to a destination schema with a different name. For example, if your plan includes change requests created for the SALES table in the FINANCE schema and you want to apply those changes to the SALES table in the FINANCE_V2 schema, then you need to map the FINANCE schema to the FINANCE_V2 schema. To map two schemas, select the source schema from the source database list and select the destination schema from the destination database list, then click the Down arrow.

   You can also specify a scratch tablespace that Oracle Change Management Pack can use for script operations that require temporary storage of data. For example, renaming a tablespace requires a scratch tablespace because all the data in the tablespace must be stored temporarily while the first tablespace is dropped and recreated. Other operations, such as operations on a table, require either enough storage space in the table's tablespace for two copies of the original table or a scratch tablespace to contain the table copy.

4. Click the Generate button to generate the script for the destination database. Work-in-progress messages display while the script is being generated.

Figure 3-5 shows a fully expanded change plan in Plan Editor with the Script subobject selected.

Figure 3-5 A Fully Expanded Change Plan

Viewing the Impact Report and Script Summary with Plan Editor

Oracle Change Management Pack creates an impact report when it generates the script. You should view the impact report to determine the impact of executing the script at the destination database. The impact report provides a summary of the number and types of objects that will be modified when the script executes at the destination database. The impact report also shows warnings and errors, including a description of the requested operations that cannot be performed at the destination database, for example, a request to drop a column that no longer exists at the destination database.

After generating the script, you should view the impact report and script summary. You can also edit the script.

• To determine the impact of executing the generated script at the destination database, examine the impact report on the Impact Report page. The impact report provides errors and warnings, including a description of the requested operations that cannot be performed at the destination database, for example, a request to rename a table that no longer exists at the destination database. The impact report also provides a summary of the number and types of objects that will be modified when the script executes at the destination database.

  Script errors must be fixed. Evaluate the errors, take corrective action, then regenerate the script.

  Script warnings should be read and the appropriate action taken before you attempt to execute the script. Some warnings are informational, for example, a message that dropping a column will cause an index to be dropped, as shown in Figure 3-5. To save the impact report to a file, click the Save As button.

• Examine a summary of the script on the Script Summary page. The script summary contains the SQL statements and non-SQL operations that will be executed at the destination database to implement the plan's change requests. The actual script includes both the SQL statements from the script and OraTCL statements. To save the script summary to a file, click the Save As button.

• To edit the actual script (not the script summary), click the Edit script button on the Script Summary page. Note that the actual script can be very difficult to understand, and editing it may result in undesired changes that you cannot undo after the script is executed. To save the script to a file, click the Save As button.

  Note that scripts produced by Oracle Change Management Pack applications can only be run using Oracle Change Management Pack applications, the Oracle Change Management Pack command line interface, or the Oracle Enterprise Manager job system. See "execute command" for more information about executing a script using the command line interface. See the online help for more information about executing a script using the job system.

A script that has been edited can be executed even if it has script generation errors.

If the impact report or the script summary is unacceptable, you can take one or more of the following actions:

• Modify the plan by modifying the directives, exemplars, propagate options, (or all of these), then save the plan, and generate a new script.

• Modify the destination database (add, modify, or delete object definitions, for example), then generate a new script.

• Change the schema mappings on the Options page, then generate a new script.

• Edit the script, then execute the script. To edit a script, click Edit script on the Script page. Note that the actual script can be very difficult to understand, and editing it may result in undesired changes that you cannot undo after the script is executed.

Understanding Script Execution

To ensure that you can undo the execution of a script, Oracle Change Management Pack makes a copy of the old data when needed for recovery purposes. The recovery data is stored in temporary tables that look like the original tables but have different names.

Oracle Change Management Pack allows you to keep or undo the changes made when a script is executed.

If you keep the changes, the temporary tables used by the recovery script are deleted, making the changes permanent.

If you undo the changes, the recovery script uses the recovery data to return the user tables and data to their original state.

Executing the Script with Plan Editor

To execute the script against the destination database:

1. Expand the destination database and select the Run subobject in the Plan Editor tree.

2. On the Execution Log page, click Execute to run the script immediately.

3. You can examine the execution log on the Execution Log page during or after script execution. The execution log displays messages, including the status of the script execution ("Script execution succeeded" or "Script execution failed"). To save the execution log to a file, click the Save As button.

4. You can keep or undo the changes made by the script to object definitions at the destination database:
   • When you click Keep, Plan Editor deletes temporary tables used by the recovery script, making the changes at the destination database permanent.

   • When you click Undo, Plan Editor uses the recovery script to undo the changes made at the destination database.

     Note: There are a small number of attribute changes for which undo operations are not present in the recovery script. The common characteristics of these cases is that the original change can be done in a single ALTER statement and the undo operation requires multiple steps. In all cases where an undo operation is not included in the recovery script: You are clearly warned, both in the impact report and if you execute the recovery script. You can still use Oracle Change Management Pack to perform the undo operation, but you must do this as a separate step. Suppose, for example, that a change plan makes several changes, including adding a column to a table. The impact report warns you that the recovery script will not drop the added column. (Note that the other changes can be undone.) If you execute the recovery script, you are warned again that the added column was not dropped. You can then use another Oracle Change Management Pack application (such as DB Quick Change) to drop the column as a separate step. The attribute changes for which undo operations are not present in the recovery script are: adding a column to a table modifying a column's datatype to specify a larger size for the column adding a datafile to a tablespace specifying a date for the Start Date or Next Date fields for a snapshot

If you want to execute the plan's change requests against a different destination database, select a new destination database, then generate, view, and execute a new script against the database.

You can also execute an Oracle Change Management Pack script using:

• The Oracle Enterprise Manager job system. See the online help for more information.

• The Oracle Change Management Pack command line interface. See "execute command" for more information.

Dealing with Script Execution Errors

The two main causes of script execution errors are:

1. Stale scripts

   When the Oracle Change Management Pack translator generates a script for a destination database, it takes the current structure of the database into account. If objects at the destination database are deleted or modified after the script is generated, the script is considered to be a stale script. Errors can occur during the execution of a stale script or recovery script. For example, if a particular user is removed from a destination database before you run a script, the execution log may display an error message such as this after the statement that generated the error message:

   ORA-01918: user `GEORGE' does not exist
   

2. Errors that the Oracle Change Management Pack translator does not anticipate when it generates a script, for example, insufficient space in a tablespace to carry out the requested changes. If you suspect that a script execution error is caused by a problem with the Oracle Change Management Pack translator, please contact Oracle Worldwide Customer Support.

To have Oracle Change Management Pack predict script execution failures (the default), enable the OCM_FAILURE_PREDICTION property in the ocm.properties file. The property is enabled when its value is set to "true" (case insensitive) or when the property does not exist in your ocm.properties file. When failure prediction is enabled, Oracle Change Management Pack performs resource checking (for example, it checks for sufficient space and quota to make copies of tables or to move items from one tablespace to another) during script generation. Resource warnings are reported in the impact report. Script generation takes longer when failure prediction is enabled.

If the property is present and has a value other than "true," then resource checking and script failure prediction does not occur.

When error messages occur during script execution, the best ways to fix the problem are:

• Click the Undo button to undo the changes. Then, regenerate the script.

• Correct the error at the destination database (for example, by creating a user again or by increasing the size of a tablespace), then click Continue to continue executing the script. If you specify a scratch tablespace, you must generate a new script and execute the new script.

• Edit the script and click Continue to continue executing the script. This is the least preferable option, because editing the script may result in undesired changes that are not reversed when you press Undo.

When error messages occur during recovery script execution, the best ways to fix the problem (in order of preference) are:

1. Correct the error at the destination database (for example, by creating the user again), then click Undo to continue executing the recovery script.

2. Edit the recovery script, then click Undo to continue executing the recovery script.

---