Recent Tweets

Entries in database (3)


Red Gate Schema Compare for Oracle Review

As part of my recent role in application release management, I have had to compare and sync various objects across Oracle databases. This was not fun work and I decided to evaluate some different tools to make this easier. The tool I have been working with recently is Red Gate's Schema Compare for Oracle. In this post I provide a brief walkthrough of its basic features and offer up my thoughts on the tool as well as how I feel it compares with other alternatives. The version of the tool shown here is

The Need

The major need I have for a database comparison and sync tool is for parallel deployments within the same environment; for example, syncing the schemas of one of our 3 development databases with another. These syncs happen at different points including when business validation starts near the end of an iteration, when parallel development is needed for application production hotfixes, or during different development or testing efforts. The other major need is for ensuring databases are largely in sync when they are expected to be, when preparing for or reviewing a deployment or troubleshooting differences in application behavior across environments.

We largely forego using such tools for a couple scenarios. First our DBAs handle larger syncs such as refreshing a development database from production. Likewise we do not use such tools for change management to deploy changes from development on up the chain to production; I discuss that somewhat in my post on the TFS Artifact Manager.

Starting a New Compare

The process starts with creation of a new project and selection of the databases and schemas to be compared.

Next there are various options that should be reviewed before starting; each has a full description to the right when hovering with the mouse. There are a couple I want to call attention to after the screenshot.

There are some options Red Gate was kind enough to add for me based on my early feedback:
  • Include SET DEFINE OFF - Some objects in my original comparisons had ampersands (&) and as a result I was getting prompted for substitution variables when running the generated script. This disables that; it is not turned on by default but I would recommend checking it.
  • Ignore slow dependencies - This has to do with type objects and REF constraints as they relate to cross-schema dependencies. This greatly increased performance early on when dealing with very large schemas. Other performance improvements have been made that have made this less important now.
  • Ignore dependent objects in other schemas - This option also was helpful in performance but I used it more to help prevent pulling in dependent objects in other schemas upfront. With the way our security is setup, we often cannot perform DDL operations logged into the database as another user. We generally compare and deploy one schema at a time, logged in as the schema owner. Using this option prevents me from manually having to exclude various objects in other schemas later on when using the Deployment Wizard.

Options - What's Missing?

When contrasted with a schema comparison tool such as what is available in Toad for Oracle, the options here seem very limited. However I personally find the 100+ checkboxes across 4 tabs in Toad to be rather dizzying and honestly I do not really know what at least a third of them truly do (some do have status bar hints on mouse-over). Also I have found with Toad that with certain checkboxes set, various Oracle errors can get thrown such as No Data Found and that can stop the whole process.

On one hand it seems logical to have options for the object types to compare. On the other hand, I do not think having that would make a big difference in performance and Schema Compare allows you to easily exclude and filter objects based on type afterwards. There is something to be said for simplicity and that is Red Gate's mantra.

One option from Toad I do really miss is "Format before comparison". In a perfect world everyone would use the same SQL formatting styles but in reality it is not easy to enforce. Many of the objects that were different only differed by formatting. The tool has options to ignore case and white space which helps but formatting differences were also around multi-line constructs in terms of wrapping, nesting, aligning and the like. I realize that full formatting functionality is far from trivial but this was a key option in my use.

Running the Database Comparison

After setting up the connection and options, clicking the Compare Now button will launch a progress dialog that allows cancelling the operation.

This is where I first ran into an issue with the initial trial version I used. Against one of our large schemas (some 3400+ objects), the tool was taking hours to run. I contacted Red Gate support to detail the performance issue as well as some other suggestions I had. They were immediately responsive and had me turn on verbose logging to troubleshoot the issue. Within the same business week they had a new non-public build of the application for me to try which had the new options mentioned above on performance, cross-schema impacts, SET DEFINE OFF, etc. The level of support they provided me is pretty much unheard of in my experience.

The performance options helped but running the tool was still very slow compared to minutes with Toad. I was told one reason for this was because Schema Compare builds a full dependency tree to script things in a correct order whereas I believe Toad and some other tools make no attempt to do this. In some cases order is not a critical as PL/SQL objects and views can be easily recompiled afterwards. In other cases it is more important and either way it makes the process smoother.

In looking at the log file and database activity, we could see the SQL the tool was generating. At that point our resident SQL optimizer Jim Tilson was able to refactor the SQL to make very significant performance improvements, in one case from around 10 minutes to under a second. We sent the SQL modifications back to Red Gate and again they quickly implemented these changes and provided us new application builds that dramatically reduced the time to reasonable numbers.

In the end it turns out most of our performance issues were largely because we are still using an ancient 9i database due to a legacy app or two. I believe stats are not automatically gathered for the SYS schema on 9i and doing so almost implies logging in as the SYS schema which is far from ideal. Performance should be fine with gathering SYS stats on 9i or using an Oracle database from the past decade which handles this automatically.

Currently the tool is running at < 2 minutes for a schema of ~800+ objects and around 28 minutes for one of 3400+ objects. However this is atypical in that we are still on 9i, haven't generated statistics on the SYS schema, have a fairly large data model and we are running database restores at the moment. For most the execution time should be minimal.

Reviewing the Differences

When finished the differences are broken down by difference status and there are various options for grouping, filtering, sorting and searching. Selecting an object presents a nice SQL difference viewer to navigate each difference.

If Schema Compare was closed at this point, the options would be saved but the comparison results would not be. A snapshot feature is provided which will do just that but it would be convenient if the app automatically created that snapshot, or at least prompted to do so.

Each differing object can be checked or unchecked individually or in batch to determine the contents of the sync script to be generated. Once the desired items are included, the Deployment Wizard can be invoked to build the sync script.

Building the Sync Script

The deployment wizard walks through the major steps:
  1. Deployment method - The tool can execute the sync script or can just display, save, or send it to another program to be run. Personally I recommend coping the generated script to the clipboard an running it in the DBMS de jour, be that SQL Plus, Toad or what have you. If the script is executed in Schema Compare, any error can halt execution and it can be difficult to know where the error occurred (exact object / SQL).
  2. Review dependencies - Allows including objects that reference or are referenced by the items chosen to be deployed
  3. Configure script - Optionally shown if there are deployment issues to be addressed; allows choosing different actions to control the behavior.
  4. Review script - Shows the deployment script and a summary of changes, as well as any warnings that may need to be considered.

Running the Sync Script

The sync SQL that Schema Compare generated ran without issue overall. Early on I did receive a permissions related error but that was because I did not realize I had included an object in another schema other than the one I was logged into to run the SQL. The options Red Gate added on my behalf helped avoid this going forward but others may not have this issue if their Oracle security setup is more cross-schema friendly. The other problem was being prompted on bind variables early on but again, the SET DEFINE OFF option that was added solved this issue.

A Nice Bonus Feature

When writing this post at one point I noticed a game of Asteroids running. I did not know where it came from, whether it was an easter egg somewhere, a virus, or an accidental launch of a game I did not know I had. After a minute of research I realized that it was an easter egg game that pops up from Schema Compare's About screen if it is left open for at least 30 seconds or so. Perhaps I should not give that surprise away but it is a fun game and a cool little nugget. Now if only this could be launched during a database compare to pass the time :).


Competition makes for better products and I have tried a handful of Oracle schema tools so far. I am sure there are other good tools in this space and that further use of and updates to the tools I have tried may change my outlook. What follows are my thoughts on similar tools and how Schema Compare stacks up in my opinion.


Toad is what I started out with since it is what I already had. It may be the most common way of doing this, executes quickly, and has the most functionality and flexibility. Toad allows comparison of far more object types than Schema Compare (with many more options) so Red Gate's tool will not detect differences to less common items such as queues, jobs, DB links and the like. Toad's extra features do come at the cost of "complexity" and you will rarely hear me say the words simple, intuitive, friendly, modern or "standard Windows experience" when talking about Toad. The exact opposite is true with Schema Compare and other Red Gate products.

The main roadblock with Toad was that the change script it generated was display-only so I could not do anything with it. I tried some clever ways to hack around that limitation but it wasn't worth it or fair to Quest :). The Toad DB Admin Module is required to copy / save / use the sync script. The price I heard for that module is around $800 for one user but I believe it is also tied to the version of Toad being used; our 9.X DB Admin module key didn't work in 10.X. The price is not prohibitively expensive for all the module provides but with just wanting one feature, it is harder to justify with no a la carte option.

Corporate spending requests can be difficult to get approved and with Quest maintenance support contracts, keeping the functionality active across Toad versions could be a roadblock. Further the Quest websites make no real mention of how you actually get this module if you want it. You pretty much have to figure out who to call and hope you do not get stuck with a high-pressure, low-tech salesman. Schema Compare is currently priced at $395 for a single user; I was fortunate enough to get a free license due to the level of feedback provided on the product.

The other Toad issue previously mentioned is that certain combinations of options generated Oracle errors in my usage in the past. To be fair, some of those issues may have since been fixed and some may only appear depending on the schemas it is being used against.

Schema Compare did appear to have a fuller dependency tree over Toad in terms of script ordering and deploying related objects. Toad claims it does the same even if it may be a bit more under the hood; in practice it was difficult to compare the results between products here so take this with a grain of salt. Schema Compare's Deployment Warnings, Conflicts, and general experience did seem friendlier than Toad's "better know what you are doing" feeling.

At this point I would be remiss not to mention Quest's Jeff Smith (blog | Twitter) who is an excellent technical Toad and Oracle resource and can be a helpful buffer with the Sales team. Our relationship is based entirely on my complaining about Toad over Twitter and he has been most gracious in helping and putting up with me.


Tool for Organizing Your Schemas (TOYS) is a lightweight, functional Oracle schema compare and sync tool. The main advantage for many is that it is free. On initial use I ran into some sort of configuration issue. The tool claims to be intuitive but I did not find that to be the case, at least not compared to Schema Compare. TOYS is no-frills but certainly provides value. One deal breaker for my use was that TOYS does not provide any way of selecting which objects get synchronized and workarounds such as editing the SQL script were not acceptable to me. I also did not like some of the generated SQL I found such as unnecessary object DROPS when CREATE OR REPLACE would suffice. Other limitations in the tool's documentation gave me pause as well. Some level of support is offered with the app but I would not expect a high level of support with a free tool.

Devart dbForge Schema Compare

DevArt's dbForge Schema Compare sells from $150. I did not get a chance to really use it as it did not work on a 64 bit OS, at least not with TNS connections. That immediately turned me off the product, combined with a bad taste in my mouth from prior use of some Devart products to use Entity Framework with Oracle. When the x64 issue is fixed I may try the tool again.

Completing the Picture

I also evaluated Red Gate's Data Compare during this process. A review here is out of scope but I found it worked nicely and complemented Schema Compare well. Unlike Toad it does not require setting up annoying database links and I found it much friendlier to use for comparing data. If you already own Toad however you get the functionality for free whereas Data Compare is another $395. If you buy both products I believe there is a discount but at that point the pricing is equivalent with Toad's DB Admin module. Toad's export as merge statements can also be used as a quick and dirty form of data comparison and sync.


Overall I am quite pleased with Red Gate's Schema Compare. The tool is in a good middle ground to me; it is not the cheapest nor the most expensive, not the most powerful but certainly not the least. Schema Compare gets the job done and in the simplest and most intuitive fashion. I cannot express how great Red Gate's team has been to work with; Tom Harris and Red Gate's team have provided an excellent level of customer support. The app could still use some more performance improvements, at least for older databases. If some additional features can be added without hurting performance or the application's ease of use, this tool will be even more attractive.


  • 10/17/2011 - Updated information on the Schema Compare vs Toad comparison in regards to the dependency tree and script ordering.

Slow OracleConnection Creation

This afternoon I executed a simple DDL statement with Oracle.DataAccess.dll and noticed it took upwards of 30 seconds when it should have come back in a second or so. In stepping through the code I noticed the large delay was in creating the Oracle Connection, not connecting to the database or executing the SQL:

Oh Oracle you are the snake to my mongoose, or the mongoose to my snake. Either way it's bad.

I vaguely remembered a similar issue years ago that had something to do with a logging directory not existing. I no longer had the email I sent with the details nor could I find the information online. So I fired up Process Monitor and found several writes to the below files:
In inspecting the log files I found hundreds of occurrences of the below error:
Directory does not exist for read/write 
[C:\app\username\product\11.2.0\client_2\log] []
This was further confirmed in Process Monitor entries:
1:52:13.8086867 PM	OracleScriptExecutor.vshost.exe	8556	CreateFile	
C:\app\username\product\11.2.0\client_2\log	NAME NOT FOUND	
Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, 
Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a
At first I just tried creating the Log directory but that didn't work. I checked security on the directory but it seemed sufficient. To resolve the error I had to create the folder structure "log\diag\clients" in my "C:\app\username\product\11.2.0\client_2\" directory.

Afterwards I could get through the OracleConnection ctor in a reasonable time:

With the details in hand I changed my web search and discovered this Oracle forum entry posted 3 years ago! 3 years and this problem is still not fixed? Come on Oracle, get it together.

TFS Artifact Manager

The What

TFS Artifact Manager is a tool intended for downloading application "artifacts", such as database scripts and report files, from TFS. It pulls down these files from task source control changesets and attachments, organizes them, outputs task and change information, and helps combine and package database object changes. The tool's primary value is doing this in mass for a work item such as a Project work item, where it will enumerate all linked scenarios, issues, bugs, and tasks, providing the complete set of source code changes that need to be deployed for a given software release.

The Why

The .net application code side of our release management process is fairly automated and painless, with the exception of merging some code in TFS such as certain XML-based files such as project files. The same has not been true with other project items such as reports and especially database changes.

Our continuous integration process does automatically deploy report changes on checkins in certain environments, and it pulls down and combines source controlled database changes. However that only solves part of the problem and managing these artifacts has historically been painful to say the least.

The History

This tool was created by the great Bryant Brabson to help pull together these changed artifacts for a given software release. Upon his sad departure I became the next victim of managing the release engineering for this large operational app. I took what was started and made a number of large additions and changes where I could find the time between deployments. While this utility is somewhat technology and process specific, it is not company-specific per se so I decided to release the source. However some changes would likely be needed to adapt the tool to different processes.

The Setup

Some details on the setup for the app I have been using this tool with are below. The tool does not really care what database or reporting system is in use; it is simply pulling changeset files and attachments where the file extensions match what is configured for the associated work items.
  • Source Control, Work Item Tracking: TFS 2008 with MSF Agile process template
    • Not tested against TFS Server 2010 yet but probably works
    • Older, customized version of MSF Agile process template
    • Some customizations likely needed depending on process template details (related work items etc.)
  • Database: Oracle
    • Database file extensions are configurable
    • Database Packager view allows selection of Oracle/SQL Server for previewing SQL (syntax highlighting)
  • Database Source Control: Toad for Oracle using TFS MSSCCI provider
    • Hopefully native use of Toad's TFS provider soon
  • Reporting: Sql Server Reporting Services
    • Report file extensions are configurable

Configuring the Tool

Some one-time configuration is available in File--> settings, namely the TFS server value.

In the app directory there are files ReportFileTypes.xml and DatabaseFileTypes.xml that specify the file extensions that help categorize and organize the artifact changes. These are also content files in the main source code project and get copied into the bin folder when changed.
<?xml version="1.0" encoding="utf-8"?>
<FileTypeInfo TypeName="Database" FileType="Database">
    <FileExtension Category="Packages" Extension="PKB" Name="PACKAGE BODY" />
    <FileExtension Category="Packages" Extension="PKS" Name="PACKAGE" />
    <FileExtension Category="Functions" Extension="FNC" Name="FUNCTION" />
    <FileExtension Category="Procedures" Extension="PRC" Name="PROCEDURE" />
    <FileExtension Category="Triggers" Extension="TRG" Name="TRIGGER" />
    <FileExtension Category="Views" Extension="VW" Name="VIEW" />
    <FileExtension Category="SQL" Extension="SQL" Name="SQL" />

Running Mass Download

Work Item Selection
A mass download is started by keying in a valid TFS work item id. It is intended to be a project work item but any work item suffices. The tool will validate the work item id and retrieve the title but adding a dialog to select a work item via a team or personal query or similar would be a nice enhancement.

Source Control Exclusions (Artifact Path Filtering)
Next a set of source control exclusions may be entered to exclude source code paths that do not have artifacts, such as branches containing .net application code. The tool could be changed to instead only supply paths where there are artifacts, or to ignore or include based on file extension but this fit our needs. The only paths that might need entering are ones that have changesets linked to the root work item through related work items. It will not hurt if some code paths are included; the changeset files will just get dumped into an Other folder. Currently the exclusions field is a simple newline-delimited field and I have not yet added the ability to browse and pick source control paths.


A Fetch button initiates the processing and processing time depends on the size of the release, determined by the number of linked work items, the number of changesets in the associated tasks, source control exclusions and file type configurations. For a maintenance release of around 80 tasks consisting of 500+ changesets, processing time was usually just under 2 minutes.

The link relationship between work items that the tool currently expects is:
Root Work Item (i.e. Project) => Scenario or Issue or Bug => Task
The root work item does not have to be a project work item type but the rest is fixed. A predicate specifies the condition for the level between the root work item and the tasks. Small changes could be made if there are additional levels between the root work item and the tasks (product backlog items for example).

Mass Download Output

When the mass download completes, it displays a log of the processing details and opens the folder it created which by default is relative to the exe location.


Files are organized into the database folder based on matches with DatabaseFileTypes.xml. The schema/database is determined via everything up to the first "." in the filename. Within the schema folders are subfolders for each of the database object type changes (views, functions, etc.), according to the configuration and the found changesets. In each of these folders reside the files of the DB changes which are current to the last (most recent) changeset in discovered tasks which are not closed. Changes found in tasks not Closed are moved into an _Incomplete subfolder per object type. Likewise deleted objects are moved into _Deleted subfolders.

For each of the DB object type folders, a combined script is created (i.e. SCHEMA_A.Views (10).sql) when more than one change was found. Each database script file is also tagged with information on the last changeset made, with some exceptions.

In the root database output folder the app produces a DatabaseChanges.xml metadata file with detailed information on each database change. This is mostly for use by the Database Packager view in the app, allowing packaging the database changes at a later date than when the mass download was performed. More on the Packager view shortly.
<?xml version="1.0" encoding="utf-8"?>
    <RootDatabaseFolder>C:\temp\TFSArtifactManager\2011\08\WI 21827\16 Tue 13-28-53\Database</RootDatabaseFolder>
  <Changes Count="153">
    <Included Count="0" />
    <Excluded Count="153">      
        <Tasks Count="1">
          <Task Id="21939" AssignedTo="Geoffrey Hudik" State="Closed" Title="Global text change for LRMO" />
	  <!-- remaining changes removed for brevity -->

Attachments are downloaded into an Attachments folder in the output path with a folder per TFS task with attachments. Task attachments with a .sql file attachment are also copied into an _Attachments folder under the Database folder; these are sql files that may need to be run for deployment but are not source-controlled changes (data migratation scripts etc.).

Reports are simply all pulled down into a single folder according to any changesets with file changes matching ReportFileTypes.xml. Incomplete and deleted changes are organized the same as database changes are.

Task Info Files
In the Tasks subfolder various text files are created with information on tasks for the release as well as artifact changes by task. Below is a partial sample from one such file:
32 Closed Tasks with Artifacts
267 Artifact Changes

Global text change for LRMO
TFS #21939 [Closed] by Geoffrey Hudik
	[Task Attachment]

	[Task Attachment]



Artifacts by Task

Artifact changes are also output per-task as well. This does create some file duplication but I often found it helpful to have these file changes together by task, especially for subsequent, partial releases. For initial deployments, releasing by task would often mean deploying some of the same objects multiple times.

Mass Download: Database Packager

The Database Packager view provides the following functionality:
  • Viewing and filtering a list of database script files generated
  • Previewing script files in a popup SQL editor
  • Selecting which database script files to include for a deployment. i.e.:
    • choosing a combined views sql file instead of each individually
    • excluding new triggers that may be included in new table scripts
  • Specifying the order in which to run script files
    • Order is often ignored for most PL/SQL objects such as views, packages, functions
    • Invalidated objects can be fixed with recompile after all is deployed
    • Mostly used for schema changes such as table alters or additions, data migration scripts
  • "Packaging" the included script changes to a directory with filenames prefixed numerically to indicate run order

Click image for larger version

This view is accessed initially via the Database Packager button next to the Fetch button once the mass download is complete. It can be accessed later via menu or toolbar and using the Open toolbar button and browsing to DatabaseChanges.xml in the Database output folder.

Use of the tool is pretty straightforward; filter, sort, and inspect changes at the top, select appropriate changes and click Include (Insert). The order the items are included will be the order they are packaged but they can be rearranged individually with Move Up (Ctrl+Up) or Move Down (Ctrl+Down). Drag and drop can also be used to rearrange the Included items though it is a little flaky at the moment. Any SQL attachments to be included must have the schema value set as scripts are packaged per schema. Clicking Package will copy and rename the included items to a publish folder; there is currently a gap of 5 in the numbering prefix in the filenames to allow for potential manual insertions/rearranging later.

All blue links may be clicked to open the file, folder, or TFS item. Filenames may be clicked (or press spacebar on keyboard) to preview selected database file(s) in a sql viewer. The Database Type combobox in the toolbar controls what syntax to use for syntax highlighting (PL/SQL or T-SQL).

Single Work Item Download

Mass download may be the most useful for a major release but often where I work there are various ad-hoc, mini-deployments such as ad-hoc reports or small database changes. Changed artifacts for a single task are retrieved using a work item changeset retriever that can be accessed via a console app (ChangesetRetriever.exe) or GUI via the Work Item command in TFS Artifact Manager:

File output for a single task is simplified over mass download and might look like the below example:
Despite both the mass and single work item download performing similar functions they were developed separately and share very little in terms of code and requirement needs.

Artifacts downloaded, now what?

On the report side we have a powershell script and an exe that works with SR.exe to deploy SSRS changes. On the database side I've started a SQL editor that allows selection of multiple databases to run the SQL against that I use among our 6 Dev and Test environments; Quest Script Runner or Toad's Script Manager sort of work but I found their use painful. For environments beyond Test I hand the packaged scripts to our DBA to be run with whatever voodoo they do. It did not quite make sense to me to link these deployment tools with the TFS Artifact Manager at this time. The database deployment tool has a long way to go before it is ready for release but perhaps someday.

Future Enhancements

One thing that would be nice is supplying a date or other criteria to the mass download piece to only download changes within a certain window. For additional deployments I currently have to either cherry-pick new changes I know about or re-deploy everything (which usually is not feasible). Likewise with the Database Packager there is currently no way to "merge" a package of database scripts from a prior run of the tool with that of a later one.


On the database side of things there are some tools I have started looking into that might eventually replace most use of this tool for database changes. These include:
The primary hold-up at the moment is price. I believe Toad Extensions runs around $4k and the change management pack requires Oracle's enterprise edition which is another ballpark of cost entirely.


Much of the source was written in small breaks in-between deployments in a rush so it could certainly use some refactoring. The code and bits are available at