Skip to Main Content
  • Questions
  • Data differencing between two Oracle Schemas

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Srinivas.

Asked: December 27, 2003 - 7:55 pm UTC

Last updated: November 30, 2005 - 8:49 pm UTC

Version: 9.x.x

Viewed 1000+ times

You Asked

Tom,

Thanks for your valuable suggestions and comments.

Even though data differencing and synchronization is a standard requirement, there are not any standard tools to do that task.

I am talking about windiff like tool to find differences between two oracle schemas.

I tried my level best to find one such tool/utility but could not find till now. I could only find schema differencing and synchronization tools. In the last project we had to develop one such internal tool (but is very expensive to develop such tools and maintain them for small companies).

If you know any such tool to do data differencing and synchronization, please let us know.

thanks,
Srinivas


and Tom said...

is it a standard task?

I do not believe so, in fact, I've never had a requirement for it in my experience.

But, there are literally hundreds of tools. Oracle Designer is one such tool. Toad has capabilities in this area as well. OEM (enterprise manager) change mgmt packs do this.


But, again, I've never actually seen the need in a well run shop for such a tool! If you use configuration mgmt techniques from the get go, you'll KNOW what the differences are at a glance, for this information would all be continously catalogued and recorded.

I'll publish this one and we'll start collecting the tool names that do this (others will add them I'm sure).

The list begins with

a) designer
b) OEM
c) toad (i think)

Rating

  (22 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for your reply

Srinivas, December 29, 2003 - 11:25 am UTC

Yes, I agree that there are many tools out there for finding differences between two schemas in terms of DB objects but not the data itself.

After doing some more extensive search could find some tools to do the data differencing and synchoronization, but not getting dev tools those can be embedded into our J2EE based system.





Tom Kyte
December 29, 2003 - 12:58 pm UTC

now I really know it is NOT a standard task. (and not really at all what you described)

we have tools for performing replication.

You can use MERGE to merge changes from table 1 into table 2.

but this is by no means "a standard" task. But even so, the simple merge command is you "windiff". windiff tells you how to make file 1 be file 2 or file 2 be file 1. thats what merge does.

(A-B) U (B-A)

Sami, December 29, 2003 - 12:15 pm UTC

For example,

Schema-1
========
Tab-1
c1
c2

Tab-2
col1
col2

Schema-2
========
Tab1
c1
c2

Tab2
col1
col2

I think, Srinivas is looking for something like below for the entire schema objects.

select * from (
select * from tab-1
minus
select * from tab-2
Union
select * from tab-2
minus
select * from tab-1
) a order by primary_key;


Also check out...

A reader, December 30, 2003 - 10:25 am UTC

the following link... might be of help.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:941629680330, <code>

Again this is to comapare the structure.

A reader, January 08, 2004 - 3:12 pm UTC

The original poster is looking for some kind of script/tools to compare DATA between tables in TWO schemas. NOT data structure/definition comparison.


Tom Kyte
January 08, 2004 - 3:38 pm UTC

interestingly....

the method to find the differences between two structures outlined in the link you take exceptoin to -- just happens to show exactly

HOW TO FIND the differences between the DATA between table in TWO schemas

cause, the method I used to report on differences says "hey, find the diffs between user_tab_columns and find the schema differences.

Not much different from "hey find the diffs between these two tables"

Found some tools

Reader, January 08, 2004 - 4:13 pm UTC

1) for SQL Server
</code> http://www.dbbalance.com/home.htm

2) for Oracle 
http://www.dkgas.com/oradbdiff.htm <code>



How to implement Version management of RDBMS data?

Mike, January 08, 2004 - 4:45 pm UTC

One of my customer has this requirement.

They start working with a base dataset (can be called version 1.0). They keep doing modifications to the data (it is for analysis purpose, no transactions). After some time, they base line again (like label in visual source safe, it is version 2.0) and continue working.

They need a tool to compare the data between Version 1.0 and Version 2.0 and it will help them in their analysis.

Storing all versions in database itself could be one option. But as the data size is huge, also they have hundred of datasets (with different versions), it may not be practical to store the data in database itself.

We have version management tools for text files but not for database. Can some one suggest me alternatives?





Tom Kyte
January 08, 2004 - 8:24 pm UTC

<quote>
They keep
doing modifications to the data (it is for analysis purpose, no transactions).
<quote>

that contradicts itself

a) they keep doing modifications to the data
b) no transactions

A and B are not possible simultaneously.


Databases can get quite large. This ain't dbase. People buy databases to do exactly what you describe.

it would NOT be practical to store the data anywhere OTHER than in the database.

Re:How to implement Version management of RDBMS data?

A reader, January 08, 2004 - 8:45 pm UTC

Just try Change Manager which is part of OEM and third party software like BMC (</code> http://www.bmc.com <code>

Re:How to implement Version management of RDBMS data?

A reader, January 08, 2004 - 8:56 pm UTC

Mike,

Perhaps they're looking for a data warehouse that stores history of data, but they just don't know it yet... It seems in line with what you've described in your post.


How to implement Version management of RDBMS data?

Mike, January 08, 2004 - 10:47 pm UTC

Tom,

Thanks for reply. What I meant by "NO transaction" is that it is not a transaction oriented system and users modify changes to their own datasets. Each dataset is stored in a separate Oracle user.

Clearcase, VSS, CVS type of configuration management tools store the base version and keep storing changes as delta.

Is there any such feature available in RDBMS world is my question.



Tom Kyte
January 09, 2004 - 8:18 am UTC

you may be interested in this:

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96628/toc.htm <code>



How to implement Version management of RDBMS data?

Mike, January 09, 2004 - 9:00 am UTC

This it is. I got a infrastructure to work on version management in our product. I will experiment more on this and will give an update in a week.

Thanks Tom for the great info.




Very Nice

Gabriel, February 10, 2004 - 4:25 pm UTC

Hello,

I was able to configure change manager scheduled compares between a database and a baseline using the command line interface and scripts. But then do I have to login every morning to check the reports? Is there a way to configure the sending of emails in case there are differences, or if not, just the emailing of the reports? UNIX or NT solutions are welcomed.

Thank you very much,

P.S. I would have liked to come to your conference in Montreal, but I couldn't. Hope to see you again soon in Montreal.

Tom Kyte
February 11, 2004 - 8:36 am UTC

if you have a script (shell script I assume) doing this -- sending email from it should be easy? just mail user@domain < report.txt? or am i missing something?

No report.txt

Gabriel, February 11, 2004 - 10:30 am UTC

Hello,

As far as I know, I can't generate text file reports from the command line. The only way I know to look at the reports is to connect in the repositiry schema and inspect them through the Change Manager GUI. These are the commands to genereate the reports that I'm using:

ocm login -idOEM_REPOSITORY
ocm compare MG1CET_BCUN

Is there an option to specify a file to write he report to? Then there would be no problem. But otherwise the info is in oem_repository tables with very unmeaningfull names in BLOB format. In this case, do you have a script that would read this info and display it in a meaningfull way?

Thank you very much,


Tom Kyte
February 11, 2004 - 12:12 pm UTC

not that I'm aware of -- I do not use that particular feature myself (so have no real practical experience with it).

Also check out...

Philippe, February 11, 2004 - 10:31 am UTC

Try oracle Tool free software on

</code> http://www.orafaq.net/links/Products/ <code>

Does it do compares

Gabriel, February 11, 2004 - 11:29 am UTC

Hello,

Does this OracleTool perform scheduled comapres between a database and a baseline?

Thank you,

Does it do compares

Philippe, February 11, 2004 - 11:55 am UTC

No, there is no scheduling function today,

but you can compare two schemas as often as you want, selecting objects you want to compare.

Maybe for futur version,it may be a good idea post it on the site

Then I can't use it

Gabriel, February 11, 2004 - 12:19 pm UTC

Hello,

I need scheduled compares that will email me the results, nothing less, nothing more. TOAD has this functionality but it has bugs. I am currently looking for alternatives. I would love to get the Oracle Change Manager to do this somehow as I believe any third party tools are invariably inferior to using Oracle native products.

Thank you for your response anyway.
So Tom, is there a way of doing this with Change Manager?

Thank you again,

Tom Kyte
February 11, 2004 - 2:43 pm UTC

not that i know of (i'd be interested in the "environment" you work in where by you have to be notified of a schema change. Seems that perhaps "i want to audit things and send myself an audit trail report" or "i need better CM on my systems" would be better then "hey, found differences, now, figure out why").

I honestly know of no one else with this need -- I'm curious about the circumstances of having such a 'need'.

i want to audit things and send myself an audit trail report

Gabriel, February 12, 2004 - 11:00 am UTC

Hello,

On this system, with 5 database,2 testing, validation, integration, production, everybody has all the psswords and this cannot be changed as they are all hard coded in all 25 3rd party tools. Sometimes, people end up running scripts in the wrong environment. Cannot change passwords, cannot change grants, cannot restrict access to the machine cannot etc.
We basically cannot guard against mistakes but we would like to know when they happen. In the beggining we tried to use TOAD which has this specific feature (automatic compare against a baseline and emailing of results ;if they implemented it means that there are other people need it) but it has bugs. We then tried to use Oracle Change Manager which seems to implement the same functionality. But now, after I read a bit about auditing this seems like a good idea. In the beggining I chose change manager because at a glance it has a smaller performance impact.
To conclude, from what I understand, you recommed auditing instead of using change manager? Do you have any scripts that get the info from the auditing views in a nice format?

Thank you very much,

Tom Kyte
February 12, 2004 - 12:03 pm UTC

Maybe DDL triggers (capture alters, creates and the like -- audit them yourself)

or audit DDL changes and have them emailed to you on a recurring basis.


It would be a way of letting you know that a change had been made. You could even write a query to make sure that if a change is audited as having happened on database 1 - you verify that it was made also on databases 2...5 and send yourself an email if not.

I have no scripts to do this, no.

Compare /AUdit

johan graden, February 12, 2004 - 5:13 pm UTC

Well, I have a little homebrewed tool that does portions of this. I have found occasions where such comparos have been useful.... Email me and I'll send you the tool - you can take a look and suggest improvements. If I'm in a good mode I may implement the suggestions :)

Compare /AUdit

Johan Graden, February 12, 2004 - 5:15 pm UTC

use johan<at>graden.com. (replace <at> with @!)

Identifying Data Difference between Schema and Synchronize

B, November 30, 2005 - 2:19 pm UTC

Tom,
We have a requirement where in we need to replicate data from two databases(application Servers) to another database(Main Server). Main Server - 10g and Application Servers are 10g lite.

All three databases are working in three separate places and the application servers get connected to the main server once in a day. During this period the data that have been entered for the entire day in the application servers, should be synchronized into the Main server.

Please let me know which is the best way to achieve this. MERGE or Materialized Views.

Thanks,
B


Tom Kyte
November 30, 2005 - 8:49 pm UTC

This seems to be a candidate for replication, this is what olite is sort of designed to do.

merge would not be sufficient, you need to support insert/update/ and delete.

Try Zidsoft CompareData

Farid, September 11, 2008 - 4:30 pm UTC

Using CompareData you can schedule data comparisons and synchronizations between any two ODBC data sources that have good ODBC drivers, including Oracle. In the next release of the application ( 1.4.5 ) scheduled task results will be written to the application database so you can query the scheduled task status, number of rows having differences, inserted, updated, etc. and the application is FREE for comparing table data and is high performance, typically twice to 10 times faster than 4 GL applications. The application runs on Windows only though ( XP and higher )

Differencing tool

Stefan Pflanz, April 06, 2009 - 3:46 pm UTC

There is a powerful third party differencing / diff-and-merge tool for Oracle databases:
http://www.orbit-db.com
This tool displays and scripts out the differences between two Oracle Schemas (both structure and data) and is comprehensive and simple to use.