Click here to Skip to main content
13,897,030 members
Click here to Skip to main content
Add your own
alternative version


43 bookmarked
Posted 20 Jul 2004

Schema Compare Tool for Oracle

, 20 Jul 2004
Rate this:
Please Sign up or sign in to vote.
This small VB.NET application allows you to compare Oracle database schemas against one another. This is very helpful when making sure your development instance is the same as your production instance; especially when implementing front-end changes.

Sample Image - OracleSchemaCompare.jpg


This small VB.NET application allows you to compare Oracle database schemas against one another. This is very helpful when making sure your development instance is the same as your production instance; especially when implementing front-end changes.


In rolling out a multi-tiered application, you may have to implement new or changed .NET assemblies, user-interfaces, and database changes in a synchronized fashion. You often need to make sure your production database has the same structure as the development or test instance before rolling out the .NET changes. How many times have you rolled out a .NET change and forgot that it required a database change like creating a view or modifying a stored procedure?

That's where this tool comes in handy.

Using the Tool

This VB.NET application comes ready to run. All you need is the .NET Framework 1.1 installed and the appropriate Oracle SQL*Net drivers because the application uses the System.Data.OracleClient for database access.

The first step is to create a "baseline" structure in XML. This is typically generated from the most up-to-date database and is used to compare other databases. You can use this single baseline to compare several other instances. This is helpful when you have to rollout changes to multiple install sites that all need to have the same database structure.

In the "Create Baseline" area, enter the database connection info and click "Build". This will query the Oracle data dictionary tables and build an XML file named "Baseline.xml" in your working directory. If you have already built a baseline, the application will read the XML file on startup and display the overview information as shown above.

Once the baseline has been generated, the "Compare Against Baseline" area becomes enabled allowing you to enter database log on criteria and click "Compare". The application will query the same data dictionary tables for the "Compare" schema and compare the results against the baseline. During the comparison, a log file is created named "ReconcileLog.txt" in your working directory.

After the compare is complete, the reconcile log is displayed in Notepad for your review. An example is given below:

In this example, we can see that the compared schema is missing a table named "NEWFUNCTB". We also see that the compared schema has an extra view that is not in the baseline named "PM_TRENDS_V", and that a column on the "WORKFLOWSTEP" table needs to be added. Although the actual code inside of stored procedures is not examined, we can see that the number of lines is different in the "BE_ACCESSGROUP" package, and can assume that the package probably contains changes.

The application compares the following database structure elements:

  • TABLES: Name, Columns, Column Data Types, Column Lengths, Column Nullable
  • VIEWS: Name, SQL Length
  • SEQUENCES: Name, Min Value, Max Value, Increment By, Cycle Flag
  • TRIGGERS: Name, Type, Triggering Event, Table Name
  • CONSTRAINTS: Name, Table Name, Type, Delete Rule
  • INDEXES: Name, Table Name, Uniqueness
  • DBMS_JOBS: What, Interval
  • PACKAGES: Name, Line Count

Once the application has been run, these files will exist in the working directory:

  • Baseline.xml: Represents baseline schema. It can be read by a DataSet using ReadXml("Baseline.xml") if you want to use the data in your own application.
  • Config.xml: Contains the last usernames, passwords (encrypted), and databases. This is read on startup to populate the form fields.
  • ReconcileLog.txt: The last compare's results.

Points of Interest

It should be noted that this application compares database structure, not data. So if you need to add records to a table, for example, this application won't pick that change up.

I've tested this application on Oracle8i and Oracle9i. Earlier versions may or may not work.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Barry Etter
Web Developer
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

SuggestionGo ahead one step Pin
yashvi_rsy17-Jun-14 23:38
memberyashvi_rsy17-Jun-14 23:38 
GeneralMy vote of 5 Pin
ramyomar1-Apr-13 21:39
memberramyomar1-Apr-13 21:39 
QuestionGot baseline set but compare raises DBNull error Pin
Giles Middleton14-Nov-12 22:03
memberGiles Middleton14-Nov-12 22:03 
QuestionLicense Pin
WilcoBT3-Aug-09 22:26
memberWilcoBT3-Aug-09 22:26 
GeneralThe BIN$ Problem Pin
Perry Tribolet3-Aug-09 10:33
memberPerry Tribolet3-Aug-09 10:33 
GeneralGreat Job! Pin
fiordel15-Apr-09 8:22
memberfiordel15-Apr-09 8:22 
GeneralExcellent! Pin
mpemberton11-Sep-08 3:55
membermpemberton11-Sep-08 3:55 
Generalcompare schema tool Pin
LWarne16-May-08 9:26
memberLWarne16-May-08 9:26 
Questiongood job!!! how about all the other objects? Pin
PeterTeoh13-Mar-08 14:44
memberPeterTeoh13-Mar-08 14:44 
GeneralExcellent Work Pin
Mohamed Kaleemullah26-Nov-07 2:18
memberMohamed Kaleemullah26-Nov-07 2:18 
QuestionComparing MS SQL DB Pin
zeevf4-Aug-07 2:03
memberzeevf4-Aug-07 2:03 
GeneralNice Apps ! Pin
hankadp18-Jul-07 4:55
memberhankadp18-Jul-07 4:55 
Generaloracle.dataAccess.client, oracle.dataAccess.dll Pin
Silver-Grey13-Nov-06 5:56
memberSilver-Grey13-Nov-06 5:56 
QuestionNice Tool but not giving me details about the "Column Diff" Pin
redearth27-Oct-06 6:04
memberredearth27-Oct-06 6:04 
GeneralHigh Memory usage for this tool Pin
prateek_parashar28-Aug-06 2:09
memberprateek_parashar28-Aug-06 2:09 
GeneralOS Question Pin
The Magnet16-Aug-06 6:25
memberThe Magnet16-Aug-06 6:25 
GeneralVery useful tool Pin
sprash2510-May-06 5:59
membersprash2510-May-06 5:59 
GeneralExcellent Tool Pin
KevinAG6-Feb-06 8:13
memberKevinAG6-Feb-06 8:13 
Generalproject doesnot run. Pin
navneet_it228-Sep-05 19:11
membernavneet_it228-Sep-05 19:11 
GeneralSchema and Login might not match Pin
raineyd6-Jan-05 4:52
memberraineyd6-Jan-05 4:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.190306.1 | Last Updated 21 Jul 2004
Article Copyright 2004 by Barry Etter
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid