Schema Compare

Hi

Looking for the best way to compare two oracle schemas on Oracle 11g.  The comparisons I wish to make could be from Object difference level to view and package script difference level and possibly data level.

Was thinking of using DBMS_COMPARISON though this looks to be a data level only.

If anyone has any scripts available that would be even better.

Thanks

Andrew
alinesAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
There is a brute force method.  You can dump the contents of the relevant DBA views (such as DBA_TABLES, DBA_TAB_COLUMNS, DBA_CONSTRAINTS, DBA_CONS_COLUMNS, etc..) into separate files for the 2 sources.  Make sure a relevant where clause and order by is on your queries, then do a diff between the 2 files.

Not a pretty solution, but once you build it, it should work.

Alternately, you could do the whole thing in PL/SQL with database links between the 2 systems and check them that way.  I don't recommend that as the link would have to be through a privileged user, or the link would only connect to the current user and select from the USER views rather than the DBA views, which still may be a security concern, but less of one.
0
 
slightwv (䄆 Netminder) Commented:
The only way I know is the Oracle Change Management Pack (additional license) which is referenced in the link below.

The link below seems to have a couple of decent scripts that people have created:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:941629680330
0
 
sventhanCommented:
If you have Toad, you could use that for a comparision.
0
 
alinesAuthor Commented:
Thanks for the suggestions though the end result will be part of an automated test build
0
 
Sanjeev LabhDatabase ConsultantCommented:
For Schema comparison a number of tools are handy such as SQL Developer, Toad, PLSQL Developer. You can easily use these to compare which is quite accurate.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.