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