ORACLE: How to Compare Schemas from Different Databases

Posted on 2011-10-03
Last Modified: 2012-05-12

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!
Question by:dhite99
    LVL 14

    Expert Comment


    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
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.
    LVL 1

    Accepted Solution

    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
    Click FINISH

    Author Closing Comment

    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Extract DDL and compare was previously suggested twice.

    Any reason the points weren't split if you liked the SQL Developer idea?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now