• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

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!
1 Solution

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now