Home > SQL Developer Concepts and ... > Change Manager Support in S...
A change plan is an Oracle Change Manager container for schema object changes. Using SQL Developer, you can create change plans and populate them change information. Later, a database administrator deploys the change plan to one or more destination databases using Oracle Enterprise Manager.
A change plan can contain multiple change items, each of which describes changes to be made to a single schema object. There are three types of change item, reflecting three basic actions to be carried out at the destination database:
An Add change item adds a database object.
A Drop change item drops the database object. (The definition of the object to be dropped is copied into the change item, enabling Change Manager to check the definition in the destination database to ensure that the object definitions are the same.)
A Modify change item makes one or more changes to a database object (for example, adding a column or a constraint to a table).
To deploy a change plan to a destination database, the DBA uses Enterprise Manager to create a Change Manager Schema Synchronization from the contents of the change plan. During this process, Change Manager detects and reports conflicts between the change plan and the database. (For example, the database may already contain an object that the change plan is trying to add.) The generated script carries out operations in the correct order.
The Change Management Parameters user preferences affect Change Manager support in SQL Developer, especially how objects are compared to determine if a change has occurred and how extensive the DDL statements are when the script for deploying changes is generated.
See the following subtopics for more information related to support for Change Manager:
Change plans are stored in the Enterprise Manager repository. An Enterprise Manager super administrator has unlimited access to all change plans. However, in most cases, it is preferable to create Enterprise Manager administrators with specific Change Plan privileges and limited access to other Enterprise Manager facilities. There are two general types of Change Plan privilege:
A Change Plan Administrator has unlimited access to change plans: the administrator can create, edit, and delete any change plan. A Change Plan Administrator is typically a DBA.
A Change Plan Developer has access to specific change plans. The access may be view-only, or view and edit. A Change Plan Developer is typically a developer of database applications.
To create a Change Plan Administrator or one or more Change Plan Developers, you muse Enterprise Manager, as explained in Change Manager Actions to Perform with Enterprise Manager.
The SQL Developer user interface support for Change Manager includes the Change Management window and the Change Management Parameters user preferences.
To display the Change Management window, click View, then Change Management. The Change Management window is displayed within the SQL Developer main window.
At the top of the Change Management window are icons for the following operations:
Disconnect disconnects the selected Change Plan connection.
Pre Script lets you enter a script with statements to be executed before the selected change plan is deployed.
Post Script lets you enter a script with statements to be executed after the selected change plan is deployed.
Connection: Database connection for the Change Management repository.
Change Plan: A change plan within the selected repository. To the right of the Change Plan are icons for the following operations: Add Plan (+) to create a new change plan to the repository, Remove Plan (X) to delete the selected plan from the repository, and Refresh to refresh the window with the current contents of the repository.
Change Items: A list of change items for the selected change plan, with the following information about each item: Action, Database Object Type, Object Name, Schema, SQL Developer Connection, Enterprise Manager Connection, Global Name, Description.
To delete a change item, select its row and click the Remove Item(s) (X) icon. To update the change items display to reflect the contents of the repository, click the Refresh icon.
Related subtopics:
To store and retrieve change plans, you must create a SQL Developer database connection to the Enterprise Manager repository database. This connection typically logs in as one of the repository database users created as a Change Plan administrator or developer. Create the database connection specifying the user name and password of a suitably privileged user, and the connection details for the schema Enterprise Manager repository.
To create a change plan:
If the Change Management window is not visible, Click View, then Change Management to display the window.
For Connection, select the database connection for the Enterprise Manager repository.
To the right of Change Plan, click the Add Plan (+) icon.
In the Create Plan dialog box, specify a name for the plan, and click Apply.
To delete a change plan.
For Connection, select the database connection for the Enterprise Manager repository.
For Change Plan, select the change plan to be deleted.
To the right of Change Plan, click the Remove Plan (X) icon.
In the Delete Plan dialog box, click Apply to confirm that you want to delete the change plan.
To add a change item to a change plan or to update (refresh) the change items in a change plan, you must be connected to the Enterprise repository as a user that has Edit privileges on the plan (or that has the Manage Change Plans resource privilege). In the Change Management window, select the relevant change plan from the Change Plan list. The current contents of the plan (if any) are listed in the Change Items display.
To add a change item to the selected change plan, drag a database object from the Connections navigator (for example, a table named EMPLOYEES from a connection named HR) into the Change Items pane. In the dialog box, specify the type of change item to be created: Add Change Item, Drop Change Item, or Modify Change Item.
There are two basic approaches to capturing changes in change plans: single database or multiple databases. The approach chosen may depend on the complexity of the work and the number of developers involved.
Single database: Before development starts, the developer creates Monitor change items for all objects that might be modified or dropped. The developer then begins development work in the database -- adding, modifying, and dropping objects. As this work progresses, the developer updates Monitor change items to make them into Modify change items, or converts them to Drop change items, and creates new Add change items. At the end of development work, the change plan contains the changes that took place during the development cycle.
Multiple databases: One database represents the pre-development state and is not modified during development work. All work takes place in one or more development databases. Monitor and Drop change items are created as needed from the pre-development database, and then updated from the development database(s). Changes from multiple developers working in multiple databases can be combined into a single change plan.
You must use Enterprise Manager to perform any of the following operations related to change plans:
Creating change plan administrators and developers
Creating change plans from Change Manager schema comparisons
Deploying change plans
For information about performing these operations, see the Enterprise Manager help and documentation.
You can create change plans in Enterprise Manager and populate them from Change Manager schema comparisons. On the Databases page, select Schema Change Plans, then Create. Enter the name of the new change plan and click OK. In the resulting Change Plan page, click Create from Comparison. Select a version of a Schema Comparison, and a Conversion Assignment as follows:
For the Change From side, select the side of the comparison that represents the original state of the schema objects.
For the Change To side, select the side of the comparison that represents the developed (or evolved) state of the schema objects.
Based on your selection, Change Manager populates the change clan with Add, Drop, and Modify change items that, if applied to the Change From objects, will convert them into the Change To objects.
To deploy change plans, Change Manager uses the script generation capability contained in its Schema Synchronizations application. You deploy a change plan by creating a synchronization that applies the changes in the change plan to a database that you select. To reduce the chance of incompatible change conflicts, the objects in the database should be identical or similar to the initial objects from which the change plan's change items were created.
To deploy a change clan to a database, select the plan on the Change Plans page, and then click Create Synchronization from Change Plan. This takes you to the first page of the Create Schema Synchronization wizard. The process of creating a synchronization from a Change Plan is similar to creating a synchronization from scratch. However, in the first phase of synchronization processing, rather than comparing the source database (or baseline version) to the destination database, Change Manager applies the changes contained in the change plan to the destination database definitions (not to the actual objects). The result of this operation provides the basis for script generation and execution, the second and third phases of schema synchronization processing.
Conflicts
During change plan processing, Change Manager may detect conflicts between change items and the destination database. In general, a conflict means that the state of the destination database is not what was expected. Examples of conflicts include:
A table that is to be added already exists, and has a different definition than that contained in the change item.
A table to be modified does not exist.
A column to be added to a table already exists and has a different data type than the column added by the change item.
A constraint to be dropped from a table does not exist.
A view to be dropped exists, but has a different definition than that stored in the change item.
A procedure to be dropped does not exist.
After changes have been applied to the destination definitions, Change Manager reports the results of the operations, including any conflicts encountered.
Change Plans and Schema Synchronizations: Comparison
Schema change plans and schema synchronizations are similar in some ways, and use some of the same underlying technology. However, they have different purposes and uses:
The purpose of a schema synchronization is to make the set of objects at the destination database identical to those at the source database or baseline version. Schema synchronization compares the objects at the source and destination, and then generates a script to reconcile all the differences.
The purpose of a change plan is to carry out a specific set of changes at any destination database. The changes may apply to an entire object (Add or Drop), or to attributes of an object (Modify, for example, add a column).
In other words, schema synchronization picks up any changes that have been made to the destination object or schema and makes those changes at the destination. Change plan deployment, however, makes only those changes specified in the change items.
With schema synchronizations, there is no chance of conflict because the source definition always overrides the destination definition. With change plans, however, there is a possibility of conflict, because the specified changes might not be applicable at a destination database or might conflict with other changes.