Compare Objects Between Dev and Prod DBs

[thermidor]

What is the best tool and/or technique to compare between a Dev and a Production DB? I need to migrate objects from Dev to Prod, but I only want to move objects that have changed.

TIA,
Sven

 

[carp]

The best method I have found is to set up a DB link from one database to the other, then compare their definitions in the data dictionary.

If your Dev DB is a clone of your Prod DB, then you can look at things like the last DDL date. If that does not work for you, then for tables, you may want to compare the contents of dba_tab_columns. For code objects, I use dba_source.

An example might be as follows (assumes you are running this from your Dev DB):

SELECT d.line dev_line, p.line prod_line, 
       d.text dev_text, p.text prod_text 
FROM dba_source d 
   FULL OUTER JOIN dba_source@PROD p
   ON d.line = p.line
      AND d.owner = p.owner
      AND d.name = p.name
      AND d.type = p.type
WHERE d.name = 'MY_PACKAGE'
  AND d.owner = 'ME'
  AND d.type = 'PACKAGE BODY'
  AND (d.text != p.text
       OR d.text IS NULL
       OR p.text IS NULL);

If no rows are returned, then the objects are identical.

 

[Turkbear]

Hi,
There is this:

http://mac.softpedia.com/get/Developer-Tools/oracompare.shtml

Also see if something like T.O.A.D. can do that as well...

http://www.toadsoft.com/toad_oracle.htm

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Thargy]

sql developer has a schema comparison tool, which will do what you want.

TOAD also has such a tool, and automatically generates a script to make one schema the same as the other.

I'm not sufficiently familiar with sql developer to know if it too can produce such a script, but since it's free, I suggest you download a copy and have a look.

Regards

T

 

[thermidor]

Thanks all for the great info!