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.

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

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.  

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

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. To define a directive for the selected object, go to step 8.

   To define a delete directive for the object in the Plan Editor tree, enable Drop Object from Database on the General page. You are advised that the object will be dropped and that any changes previously specified for the directive will be discarded. When the script generated from the change plan executes at the destination database, any objects at the destination database for which delete directives are specified are deleted.

   If you specify a delete directive for an object upon which other objects depend, the impact report includes a warning that the directive object and all the objects having hard dependencies on it will be removed from the database. An object with a hard dependency on another object cannot exist if the object that it depends on is deleted. For example, an index has a hard dependency on a table. If the table is deleted, the index cannot exist. Check the impact report and script to learn which objects will be deleted and whether this is what you want.

   See the help topic for the directive General page for more information about delete directives.

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

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. Note that if the directive is a delete directive, you can specify a scope for it, but be very careful when specifying the scope to avoid deleting more objects at the destination database than you intend. After you generate the script, check the impact report and script to learn which objects will be deleted and whether this is what you want.
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 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. If your plan contains exemplars, the Copy Data Options page is available for the plan. Note that your plan must include only exemplars (not modified exemplars or directives) for the copy data options to be used.

    The Copy Data Options page for the plan controls whether none, all, or a subset of the data in the tables referenced by change plan exemplars is copied to the destination database. To display the Copy Data Options page for a plan, click the plan name in the Plan Editor tree, then click the Copy Data Options tab in the detail view.

    On the Copy Data Options page, you can specify that you want to copy:

    • Only the table definitions for the exemplars to the destination database

      To avoid copying any data in the tables, disable the Copy Table Data option.

    • The table definitions for the exemplars and all of the data in the tables to the destination database

      To copy the table definitions and all the data in the tables, enable the Copy Table Data option and the All data in all tables option.

    • The table definitions for the exemplars and a subset of the data in the tables to the destination database

      To copy the table definitions and the same percentage of the data in all the tables, enable the Copy Table Data option, the A percentage of data in all tables option, and specify the percentage to copy from all the tables in the % field.

      To specify copy data options for individual table exemplars in the plan, enable the Copy Table Data option and the Define copy data options for each table option. Then, for each table exemplar in the plan, perform the following steps:

      • Click on the exemplar in the Plan Editor tree.
      • On the General page for the exemplar in the detail view, specify the copy data options for that table.

        Note: In previous releases, the Copy Table Data option was available for all table exemplars in a change plan or on a per table basis for table exemplars. The Copy Table Data setting on the individual table level was used only when the Copy Table Data option on the plan level was disabled. In this release, the Copy Table Data option is still available for all table exemplars in a change plan or on a per table basis for table exemplars. However, the Copy Table Data settings on the individual table level are used only when the Copy Table Data option on the plan level is enabled. When you upgrade to this release, any change plan that had the Copy Table Data option disabled on the plan level and the Copy Table Data option enabled for one or more table exemplars in the previous release will have the Copy Table Data option enabled on the on the plan level and the Copy Table Data option enabled for those table exemplars in this release. This ensures that the Copy Table Data option settings on the individual table level are preserved in the plan in this release.

    For more information on specifying copy data options, see the "To Copy Table Definitions and Their Associated Data" and "Understanding Which Data is Copied When You Copy a Subset of Data" help topics.

    On the Copy Data Options page, you can also specify that you want to copy optimizer statistics from the source database to the destination database. Note that this operation will succeed only if:

    • Optimizer statistics have been generated in the source database using the ANALYZE command or Analyze Wizard
    • The source and destination databases are Oracle server release 8.1.7 or higher

    To copy optimizer statistics from the source database to the destination database, enable the Copy Optimizer Statistics Data option on the Copy Data Options page.

    Note that a change plan can copy optimizer statistics regardless of whether or not the plan also copies table data.

    Database links are used to copy table data and optimizer statistics from one database to another. If you attempt to copy table data or optimizer statistics from one database to another and the impact report includes an ERROR-level message about not being able to find a global name for the source database or destination database, this means that a database link must be created for the database or databases referenced in the error message.

11. If your plan contains exemplars, the settings you specify on the Propagation Options page will be used.

    The values of the propagation options on the Propagation Options page determine how exemplars in the plan are applied when a script generated from the plan is run at a destination database. To display the Propagation Options page, click the plan name in the Plan Editor tree, then click the Propagation Options tab in the detail view.

    You can view and, if desired, change the value of one or more of the plan's propagation options on the Propagation Options page.

    The Physical attributes section of the Propagation Options page lets you specify how the physical attributes for exemplars will be applied at the destination database, as follows:

    • If you choose Ignore, the physical attributes for the exemplars are ignored. Objects at the destination are created with the default physical attributes used at the destination.
    • If you choose Use settings of individual exemplars, the physical attributes specified for each exemplar are used at the destination database. In other words, the physical attributes of the destination database object are modified to match the physical attributes of the exemplar.
    • If you choose Override for all table, index, and cluster exemplars, you can specify the destination database physical attributes to be used for all the tables, indexes, and clusters referenced by plan exemplars.

      A typical scenario in which you might use this option is when a change plan is propagating tables, indexes, and clusters from a source database to a destination database. In many cases, the physical attributes specified in the exemplars for these objects are not appropriate for the objects after they are copied to the destination database. Therefore, on the Propagation Options page, you can specify destination database physical attributes to use for those tables, indexes, and clusters.

      You can choose one of the following methods of specifying a set of physical attributes to use at the destination database:

      • You can specify a percentage by which to scale the physical attributes for the plan's table, index, and cluster exemplars at the destination.

        For example, if you want the physical attributes at the destination database to be half the size of the physical attributes specified for the plan's table, index, and cluster exemplars, enter 50 in the % field for the Scale Storage Size by option. Or, if you want the physical attributes at the destination database to be twice the size of the physical attributes specified for the plan's table, index, and cluster exemplars, enter 200 in the % field for the Scale Storage Size by option.

        These settings are used for initial and next extent sizes.

      • You can specify custom physical attributes to use at the destination.

        To do so, choose Customize by modifying and click Physical Attributes to display the Physical Attributes Modifier dialog box, in which you specify the physical attributes to use at the destination.

    See the online help for the Propagation Options page 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. The Change Management Pack script generator translates the plan's change requests into a script that will make the desired changes at the destination database.

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.

   If you do not have the DBA role and you generate a script for a change plan, Oracle Change Management Pack places a warning in the impact report. The warning tells you that you do not have the DBA role. It also says that:

    

   • The script will succeed if it is run in your schema unless it tries either of the following:

      

     • To create objects (such as creating a trigger) for which you lack the specific privilege (for example, CREATE TRIGGER)
     • To copy data from tables for which you lack the SELECT privilege
   • The script will fail if it tries to operate on or create new references to objects outside of your schema, and you lack the appropriate privileges for those objects. If you attempt to run the script anyway, you receive an Oracle database error message saying that one or more of the database objects does not exist, and the script execution fails.
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 DBArole is set in 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

The most common causes of script execution errors are:

1. Stale scripts

   When the Oracle Change Management Pack script generator 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 related to your having insufficient privileges for operations attempted in the script. The impact report generated with the script should have already warned you about privilege problems that could prevent you from running the script. See "Viewing the Impact Report and Script Summary with Plan Editor" for more information.

   If when you run the script you receive an error message saying that one or more objects does not exist, this can also indicate that you do not have sufficient privileges for the operations attempted in the script.

3. Problems that the Oracle Change Management Pack script generator 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 script generator, please contact Oracle Support Services.

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.