Home / server / Questions / 26360
Accepted
Stuart Woodward
Asked: 2009-06-16 22:14:55 +0800 CST

How can I diff two Oracle 10g Schemas?

  • 772

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

sql database oracle oracle10g

6 Answers

  1. Best Answer

    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.

    • 14

  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.

    • 6

  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

    • 4

  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.

    • 2

  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.

    • 1

  6. If you have some money to spend, consider to use PowerDIFF for Oracle: http://www.orbit-db.com. This tools compares tables, indexes and triggers excellently und comes with a number of comparison options to customize the 'diff' operation. A total difference script (DDL and DML commands) is generated optionally. You need an ODBC driver to connect to the database.

    • 1