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.
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
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.
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
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?
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.
January 09, 2004 - 8:18 am UTC
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.
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,
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
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,
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,
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
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.