3
Using Plan Editor

The Plan Editor application allows you to modify and/or reproduce 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 Alter, DB Quick Change, DB Propagate, and the Synchronization Wizard. However, these change 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. Plan Editor can edit plans that were created earlier using the other change applications and can generate and execute scripts for those plans.

The other change applications have page-oriented interfaces that guide you step-by-step through the process of specifying the changes the tool is designed to make and making those changes. Because you can make different types of changes using Plan Editor, its interface is not page-oriented and it does not guide you step-by-step. You must learn the process of using Plan Editor to create and modify change plans that make different types of changes.

Note: This chapter explains in detail the steps for modifying and creating object definitions using Plan Editor. In the process of describing these steps, the chapter provides information about the following, which are available in other change applications besides Plan Editor: change requests (directives, scoped directives, exemplars, and modified exemplars) operations (change plan creation, destination database selection, script generation, and script execution) features (viewing grants, viewing dependent and dependency objects, and using propagation options) Therefore, when you read this chapter, you learn how to use Plan Editor effectively, and you learn about concepts, operations, and features that will enable you to use the other change applications more effectively.

Modifying and Creating Object Definitions with Plan Editor

This section describes how to start the Plan Editor application and use it to create a change plan, which is used to modify and create object definitions.

Figure 3-1 shows the steps for creating a change plan. The steps in the figure are described in detail in the remainder of this chapter.

Figure 3-1 Creating a Change Plan with Plan Editor

Text description of the illustration nu-3708a.gif

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. See "Starting Change Manager" for information on the different ways to start Change Manager.

2. On the Change Manager Object menu, click Create Change Plan.

3. In the Create Change Plan Options dialog box, click Manual Creation and then OK, which displays the Create Plan dialog box.

4. 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 (or the list of databases discovered in the standalone Console, if you are running Plan Manager standalone).

      • Provide a description of the plan (optional).

   2. Click Create to create the plan. This launches Plan Editor for the newly-created plan, which is empty when it is created.

Understanding Change Requests

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, scoped directives, exemplars, modified exemplars, or some combination of these types of change requests for the plan.

The rest of this section provides conceptual information about directives, scoped directives, exemplars, and modified exemplars.

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 used in the Oracle Enterprise Manager Console.

A directive has the scope of a single object definition. That is, the changes specified in the directive are applied to a single object definition when the change plan script executes at a destination database.

Understanding Scoped Directives

You can also 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. The changes specified in a scoped directive are applied to multiple object definitions when the change plan script executes at a destination database.

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 at the destination database, make whatever changes are necessary to that object definition so that it matches this object definition. If the object definition does not exist at the destination database, create an object definition that matches this object definition." When you define an exemplar, you can also include grants information for the exemplar.

Understanding Modified Exemplars

You can also edit the attributes of an exemplar definition before propagating it. For example, you can modify the storage parameters for exemplar definitions before propagating the definitions from a production database to a test database. After the propagation operation completes, the propagated definitions will already have the desired storage parameters for the new destination. An exemplar with attributes that have been changed since the exemplar was created is referred to as a modified exemplar. See "Creating a Modified Exemplar with Plan Editor" for more information on using Plan Editor to create a modified exemplar.

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
Synchronization Wizard	exemplars
DB Propagate	exemplars, modified exemplars,Foot 1 or both
DB Quick Change	directives
DB Alter	directives, scoped directives, or both
Plan Editor	directives, scoped directives, exemplars, and modified exemplars, or any combination of these

1 Modified exemplars can be created with either DB Propagate or Plan Editor, but Plan Editor offers more options for applying modified exemplars in change plan scripts at destination databases. See "Generating a Script with Plan Editor" for more information.

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

Note: Plan Editor is the only application that can create change plans with all the change request types. Use Plan Editor to create change plans with any combination of the change request types.

Defining Change Requests with Plan Editor

You have created an empty plan using Plan Editor. Now 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, scoped directives, exemplars, modified exemplars, or some combination of these types of change requests for the plan.

The rest of this section provides detailed information about how to create directives, scoped directives, exemplars, and modified exemplars using Plan Editor.

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. In Plan Editor, choose Plan->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 if you think you would like to add other change requests to the plan later.

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

Text description of the illustration directiv.gif

Note: 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 directive's General page. 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 all of the changes that are specified for the directive are displayed on the directive's General page.

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. Make sure that the directive whose scope specification you want to modify already has one or more changes specified for it. If you followed the instructions in "Defining a Directive with Plan Editor" to create the directive, you have already completed this step.

2. In the Plan Editor tree, click the directive for which you want to edit the scope specification.

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

4. 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 specific 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 specific 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 specific 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 specific 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 specific 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 specific schema names or wild card patterns from the search criteria, select the item in the Search For list, then click Remove.

   See the online help for more information about specifying wild card patterns.

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

Text description of the illustration scdirect.gif

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 if you think you would like to add other change requests to the plan later..

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

Text description of the illustration exemplar.gif

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.

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 will not be created at the destination database. To manually add a dependency 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 dependent object definition to the plan, select the object definition and click Add to Plan. Objects that are already in the plan are unavailable.

   By default, objects on the Dependencies and Dependents pages are displayed in a tree. If you click Show List, the objects on the selected page will be displayed in a list instead of a tree. Click Show Tree to display the objects in a tree again.

   Click Generate Report to generate a report about the dependency and dependent objects. The report can be generated in HTML or comma-separated values format.

10. You can view and, if desired, change the value of one or more of the plan's propagation options. To do so, click on the plan name in the Plan Editor tree. In the detail view, click the Propagation Options tab to view and, if desired, modify one or more values for the plan's propagation options. The values of the propagation 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 propagation options and a description of how their values affect the application of a change plan's exemplars at a destination database.

Creating a Modified Exemplar with Plan Editor

You may want to edit the attributes of an exemplar definition before propagating it (instead of creating an exemplar definition to propagate, then manually editing the definition's attributes after it has been reproduced at a destination database). For example, you can modify the storage parameters for exemplar definitions before propagating the definitions from a production database to a test database. After the propagation operation completes, the propagated definitions will already have the desired storage parameters for the new destination. An exemplar with attributes that have been changed since the exemplar was created is referred to as a modified exemplar.

To modify an exemplar, thereby creating a modified exemplar:

1. In the Plan Editor tree, expand the Change Requests folder.

2. Expand the object type folder that contains the exemplar (for example, if the exemplar is a table definition, expand the Tables folder).

3. If the exemplar is a schema object, expand the folder for the schema.

4. Expand the exemplar.

5. Click the exemplar's Attributes subobject, which displays the exemplar's attributes in property pages in the detail view.

6. On the General page for the exemplar, choose Use Modified Exemplar, which enables you to make changes to the exemplar's attributes on the property pages.

   Note: The following changes to an exemplar definition are not supported: Changing the name of the original exemplar definition Changing the exemplar's schema

Both DB Propagate and Plan Editor allow you to create modified exemplars. However, Plan Editor offers more options for applying modified exemplars in change plan scripts at a destination database. See "Generating a Script with Plan Editor" for more information.

Selecting a Destination Database with Plan Editor

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

1. In Plan Editor, choose Plan->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, the Oracle Change Management Pack application 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, a different script is generated for each database. This is because Plan Editor and the other change applications take 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 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 Plan Editor generates a script for a destination database, you can view and, optionally, edit the script. An impact report is also created when the script is generated. 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. The other change applications also generate scripts that can be viewed and edited.

If you modify a plan's change requests after you have generated one or more scripts for the plan, the scripts that have been generated are considered to be obsolete scripts. When you try to execute an obsolete script, the Change Management Pack application you are using 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. Expand the Destinations folder.

2. Expand the destination database.

3. Click the Script subobject. This displays the script property pages in the detail view, as shown in Figure 3-5.

4. Click the Options tab in the detail view. 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.

     Note: Schema mapping is not applied to SQL or PL/SQL definitions of views, check constraints, triggers, and so on. For example, if a schema mapping XXX -> YYY exists, references to schema XXX in a view definition are not changed to YYY.

   • Specify a scratch tablespace that Plan Editor 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.

   • Specify a commit interval to be used when the script runs. The specified value dictates the number of rows the script will process before performing a commit operation.

   The Options page and its features are also available from other change applications besides Plan Editor.

5. Click the Advanced Options tab in the detail view. On the Advanced Options page, you can specify how the change plan's modified exemplars will be applied at the destination database when the script executes. The two choices are to apply either:
   • The original exemplars and the specified changes (this is the default)

   • Only the changes specified for the exemplars

   If you choose to apply only the changes specified for the modified exemplars, there are several suboptions that allow you to select which changes to apply. See the online help for the Advanced Options page for more information about the options and suboptions for applying changes specified for modified exemplars.

   Modified exemplars can be created using Plan Editor or DB Propagate. However, the Advanced Options page is not available when you use DB Propagate to generate a script. Therefore, scripts generated by DB Propagate for plans with modified exemplars use the default method of applying modified exemplars (the script applies both the original exemplars and the specified changes at the destination database).

   If you use DB Propagate to create a plan with modified exemplars and then generate a script for it, you might decide that you do not want the default option for applying modified exemplars. If so, edit the plan originally created in DB Propagate with Plan Editor, click the Script subobject, select the other option and the desired suboption on the Advanced Options page, then generate the script again and execute it using Plan Editor.

6. Click the Generate button to generate the script and the impact report that describes the impact of executing the script at 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 and the Impact Report page displayed.

Figure 3-5 A Fully Expanded Change Plan

Text description of the illustration planman.gif

Viewing the Impact Report and Script Summary with Plan Editor

Oracle Change Management Pack change applications create an impact report and a script summary when they generate a script.

1. Click the Impact Report tab to display the Impact Report page. Figure 3-5 shows an impact report displayed in Plan Editor.

   View the impact report to determine the impact of executing the generated 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.

   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 Save As. To print the impact report, click Print.

2. Click the Script Summary tab to display the Script Summary page.

   View the script summary 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 Save As. To print the script summary, click Print.

   To edit the actual script (not the script summary), click the Edit script button on the Script Summary page. This displays the script in the Edit Oracle Tcl Script dialog box. 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 in the Edit Oracle Tcl Script dialog box.

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

   Note: 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.

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, propagation 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 change applications make a copy of the old data when it is 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 change applications give you the option of keeping or undoing 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 in the detail view, click Execute to run the script immediately.

   Note: To execute a script generated by any of the Oracle Change Management Pack applications, use an account for which DBA privileges are set as the preferred credentials.

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. After executing a script, the Execution Log page lets you 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 a change application 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 change 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 a 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.

All the change applications allow you to execute scripts generated by that application. Plan Editor can execute scripts generated by any change application.

Dealing with Script Execution Errors

Two common 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. Problems 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 (which is the default behavior), the OCM_FAILURE_PREDICTION property in the ORACLE_HOME/sysman/config/omsconfig.properties file must be enabled (where ORACLE_HOME is the directory in which Oracle Enterprise Manager is installed). The property is enabled when its value is set to "true" (case insensitive) or when the property does not exist in the omsconfig.properties file. When failure prediction is enabled, Oracle Change Management Pack change applications perform resource checking (for example, they check 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 OCM_FAILURE_PREDICTION property is present in the omsconfig.properties file 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.