A quick tour of TOYS

Using TOYS to compare schemas

It is best to work through an example.

Suppose you have been working on a Human Resources (HR) application and your data modeler has just released a new version of the HR schema (say "HR" version 147) and you want your development schema to agree with this new model.

Well the first thing you have to do is to determine the differences between this new model and your existing schema. That is, you will use TOYS to compare your development schema against this latest release of the HR schema.

Let us say that your development (working) schema is called something imaginative like "HR_DEV5".

So where do you begin?

Well let us assume that the data modeler has actually created the new physical model and has generated the appropriate DDL script to build it. Let us also assume that some kind soul has executed this DDL script thus constructing a schema named "HR_147".

From within TOYS, you connect as user HR_147 (or as a DBA type user if you are lucky enough to have those privileges). You then need to capture a snapshot of schema HR_147 as your reference database.

As used in this example, "capturing a snapshot" simply means that we read the Oracle® catalogs to extract all of the relevant object definitions (meta-data) for the schema.

To begin the capture, you simply choose File -- Capture and are presented with a simple wizard. You then have to choose an existing Oracle® session [or open a new one], choose the schemas to capture [only if you have DBA privileges] and finally elect the object types to be captured.

The screenshot below is an example of the third page of the schema capture wizard. This is where you select the object types to be captured.

After capturing the reference database schema [i.e. HR_147], you must then capture your working database schema [i.e. HR_DEV5]. This is identical to the process just discussed.

Note the following:

• The working and reference schemas can be in different physical databases.

• You can reverse the order of performing the capture of the two snapshots. That is, you can capture the working database schemas first.

• Once you have captured a snapshot, you can save it to file. Thus it is likely that the reference database snapshot is loaded from a file rather than being re-captured from the database.

With the two snapshots now loaded, you simply click the compare button. TOYS presents you with a simple wizard which allows you to elect compare options, etc.

The screenshot below is an example of the 1st page of the schema compare wizard. This is where you select the object types to be compared and the compare options.

Assuming the default compare options are suitable, you simply click Finish and view the differences. These differences are presented in a hierachial (tree) view.

In this differences view, the extra, absent and changed objects are highlighted. This tree view displays all objects in both the working and reference databases (DBs). In this view, the following icon symbols are used to indicate differences.

	The object is extra in the reference DB. This means that it needs to be added to the working DB.
	The object is absent in the reference DB. This means that it needs to be removed from the working DB.
	The object is different in the reference DB. This means that it needs to be modified in the working DB to match the corresponding object in the reference DB.
	The object is equivalent in the working and reference DBs. This means that no changes are required for this object.

Below is a sample differences view with the tree fully expanded.

In this particular example, equivalent objects were excluded from the view. Details of an object's differences would be displayed in a "details" pane when you click on any object with differences (i.e. those marked with a ).

Thus the Differences view indicates:

• Objects not currently in the working DB but are present in the reference DB and hence are candidates for creation.
• Objects which are currently in the working DB but absent from the references DB and hence are candidates for removal.
• Objects that are present in both DBs but are different in some way and hence are candidates for being modified.

Let us say that after viewing the differences you would like your schema (that is HR_DEV5) to match the new structure (that is HR_147). How do you change your schema so it matches [is synchronized with] the data modeler's new design?

Using TOYS to synchronize schemas

Once you have compared two schemas, you can use TOYS to generate a synchronization script. This is a script containing the appropriate DDL and DML commands to upgrade the working schema to agree (in structure) with the reference schema.

TOYS is not limited to single schema comparisons or synchronization. Multiple schemas can be compared or synchronized together. We refer to a group of schema being compared or synchronized as the working or reference database.

Once a compare has been done you simply choose the option Tools -- Synchronize. A simple wizard is presented. When you click Finish, the synchronization script is generated and written to the nominated file.

Copyright © Impact Systems Pty. Ltd. 2008