Friday, January 20, 2006

Compare 2 Oracle schema's

*** Edit: An update of this post you find here where I compared Oracle SQL Developer and Schema Compare for Oracle ***

A collegue of mine asked for a comparison tool for Oracle databases.
I searched a bit on the internet, and found the below solutions.

*** Following tools can connect first to 1 database and later to an other (didn't test this) ***
- http://www.codeproject.com/vb/net/OracleSchemaCompare.asp For windows, written in .NET (with xml!)

*** Following tools must connect to both database ***
- OEM - Change manager (used it, works great)
- TOAD - Compare schema's (used it, works great)
- http://www.dbspecialists.com/scripts.html basic SQL*Plus script with DB Link
- http://www.softtreetech.com/dbtools/ nice tools for Oracle (windows)
- http://www.impacttoys.com/download.htm Tool for Organizing Your Schemas
- http://www.dbsolo.com/schema_comparison.html Compare databases (oracle, sql server, …)
- http://www.dkgas.com/oradiff_screens.htm Compare 2 Oracle databases
- http://www.dbbalance.com/technology.htm Compare databases (oracle, sql server, …)

*** Following tools work at design level ***
You can also do reverse enginering of the schema's with the used design tool and compare from there...
The design tool we used: Sybase PowerDesigner, Oracle Designer

*** Following script will compare the data of 2 tables (with or without db link) ***
(I found it on http://asktom.oracle.com and adapted it a bit)
Download the script here.

9 comments:

rbedick said...

DB Side-By-Side (http://www.schematodoc.com)lets you create a library xml schema snapshots. You can then compare any two snapshots - either two different databases, or the same database at two different times.

CharlesK said...

Another great tool that I've used is Comparenicus (at www.orbiumsoftware.com).

Comapres data and schema.

Torrance said...

This one is free and very easy to use.

http://www.torry.net/authorsmore.php?id=2524

Stefan Pflanz said...

There is another tool: PowerDIFF for Oracle.
http://www.orbit-db.com.
Compares 2 Oracle databases or database schemas - with a lot of settings for individual comparisons. Structure and data is comparable and the result can be scripted out. It's thus a simple and comprehensive diff-and-merge tool.

David Atkinson said...

Hi,

Here at Red Gate we have a schema comparison tool called SQL Compare. We're currently developing an Oracle equivalent, Schema Compare for Oracle. To find out more and to participate in our early access program please visit: http://www.red-gate.com/products/schema_compare_for_oracle/index.

Tom said...

Red Gate Schema Compare for Oracle1.0 has been released. It compares and synchronizes the schemas of Oracle 10g and 11g databases

http://www.red-gate.com/products/schema_compare_for_oracle/index.htm

-Tom Harris

Kyle Hailey said...

There is also Embarcadero's
Change Manager which works on Oracle (and SQL Server, Sybase, DB2 an others). I believe Change Manager is superior to the other options on the market.
and Embarcadero's ER Studio for the design part.
.
For scripts I'm looking at
http://www.dbspecialists.com/files/scripts/compare_data.sql
http://www.dbspecialists.com/files/scripts/compare_schemas.sql
which I found on http://tonguc.wordpress.com/2008/03/06/10gs-ora_hash-function-to-determine-if-two-oracle-tables-data-are-equal/

SQLEDT said...

I wish to add http://www.sqledt.com to this list. It does schema/data compare, as well as intelligent test data generation.

Shalin Siriwaradhana said...

I use creately for designing database schema diagrams. I think this information will help this post.