ORACLE: How to Compare Schemas from Different Databases


I have a requirement to compare schemas (objects, column defs, etc,.etc, but not data) from two different databases. I know there are many products that facilitate this, including TOAD and OEM, but the catch is that I cannot compare the schemas 'live' - that is, I need to extract the schema definitions to file(s) and then use some kind of method to compare the files. I am not allowed to connect to both databases simultaneously.

Any ideas would be appreciated - thanks!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I guess file comparision might take lot of time if you are trying to compare offline.

Other option you can think of is to take a backup of these 2 DB's and restore it on temp server, do the comparision using TOAD, OEM, get the results and drop those DB's when your analysis is done

May be other experts can provide their comments as well
slightwv (䄆 Netminder) Commented:
You might be able to use dbms_metadata.get_ddl for each schema and if you are lucky just diff the files.

Instead of a backup/restore, create a 'compare' database and create two new schemas and rebuild the objects and compare those?  If this is an option then I would use export/import (the old exp/imp or new expdp/impdp) with no rows.
Sophia PaterakisCommented:
I've used this in the past when Toad wasn't an option, or firewalls prevented access to both databases simultaneously:

1. Use SQL*Developer export the DDL (No Data) of Database 1

2. Use SQL*Developer export the DDL (No Data) of Database 2

3. Diff the files using your favourite visual diff (WinDiff/FileMerge)

If you want to know how to create a DDL file using SQL*Developer, then:

Open SQL*Developer
Connect to the database as a DBA user (or any user that can export the schema)
Tools -> Database Export...
Select your database connection from the list
Check "Include Grants"
Check "Show Schema" (Should be already checked)
Check "Storage" (Should be already checked)
Un-check "Export Data"
Select a filename. Something like "schema.DB_NAME.2011_10_03.sql"
Click NEXT
Leave everything checked
Click NEXT
Click "More"
Select your application SCHEMA from the drop-down list
Add all objects to the capture with the double arrow ">>"
Click NEXT

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dhite99Author Commented:
slightwv (䄆 Netminder) Commented:
Extract DDL and compare was previously suggested twice.

Any reason the points weren't split if you liked the SQL Developer idea?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.