How can I diff two Oracle 10g Schemas?

Question

I have the feeling that there are some difference between two large complicated Oracle schemas which should be identical, so decided to dump and diff them to investigate. I found an article (http://www.dba-oracle.com/art_builder_get_schema_syntax.htm) which gives the following commands to dump the table and index definitions.

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;

However, when I dumped and diffed spool of the output the schemas, there were trivial differences on almost every line making the difference worthless. For example the schema name is included in each table definition.

Is there better way to do this that will give the most important schema data in a format that can be compared easily using a diff tool. I am interested in tables, indexes and triggers.

I am using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Solution 1

The free and open-source SchemaCrawler tool that I wrote will do what you need. SchemaCrawler outputs details of your schema (tables, views, procedures, and more) in a diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can also output data (including CLOBs and BLOBs) in the same plain-text formats. You can use a standard diff program to diff the current output with a reference version of the output.

https://www.SchemaCrawler.com

You will need to provide a JDBC driver for your database.

Solution 2

you don't need any of this stuff.

otn.oracle.com

Oracle has a free tool called SQL Developer. it has a schema diff.

Solution 3

You could use TOAD is a great generic tool for Oracle development. There is a trial version for testing. From the features list:

• Data Compare and Sync wizard
• Synchronize data across database platforms

Another product you could test is OraPowerTools:

OraPowerTools is a collection of native oracle database utilities. This collection includes OraEdit PRO, a complete Oracle Development environment, DBDiff for Oracle, compare and upgrade any 2 oracle databases, and DBScripter for Oracle, create sql scripts out of your oracle database's objects and/or data.

As free alternative you could check out this CodeProject article: Schema Compare Tool for Oracle

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.

1: 1: http://www.toadsoft.com/toad_oracle.htm

Solution 4

We use the DKGAS 'DBDiff for Oracle', it can compare an entire schema or part thereof (tables, sequences, indexes, constraints, privileges, packages, objects, and data), as either a comparison report or as an upgrade script.

We use as the latter as the basis for database upgrade scripts.

Solution 5

If you're able to run Perl, you could look at SQLFairy, which should be able to produce a dump of your schema as SQL. YOu can then use standard text diffing tools. There is also a tool which will take two schemas and produce the ALTER commands to make them identical.

View more solutions

Comments

[Sualeh Fatehi]

I realized much later that that the FAQ's say that you need to disclose your affiliation with the open source project, and I have started doing that now. However, I do stand by my answer, and think that it is a solution worth exploring. I have used SchemaCrawler in two of my past companies, and both the development teams agreed that it was a useful tool for this purpose. (No, I was not the boss.)