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
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.