Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

6 Answers 6

up vote 12 down vote accepted

The free and open-source SchemaCrawler tool 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.

http://schemacrawler.sourceforge.net/

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

share|improve this answer
    
I'm voting this as best answer as Sualeh is the developer of this tool and was so modest that he didn't mention it in his reccomendation. The other answers were very helpful too. Thanks everyone –  Stuart Woodward Jun 18 '09 at 9:51
1  
Humm. Do we need a Shill Badge? :) –  EvilTeach Mar 4 '11 at 17:10
1  
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.) –  Sualeh Fatehi Mar 5 '11 at 0:32
add comment

you don't need any of this stuff.

otn.oracle.com

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.