Thanks for your reply
December 29, 2003 - 11am Central time zone
Reviewer: Srinivas from Virginia, USA
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.
Followup December 29, 2003 - 12pm Central time zone:
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)
December 29, 2003 - 12pm Central time zone
Reviewer: Sami from NJ,USA
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...
December 30, 2003 - 10am Central time zone
Reviewer: A reader
the following link... might be of help.
http://asktom.oracle.com/pls/ask/f?p=4950:8:4674014385171550577::NO::F4950_P8_DISPLAYID,F4950_P8_CRI
TERIA:941629680330,
Again this is to comapare the structure.
January 8, 2004 - 3pm Central time zone
Reviewer: A reader
The original poster is looking for some kind of script/tools to compare DATA between tables in TWO
schemas. NOT data structure/definition comparison.
Followup January 8, 2004 - 3pm Central time zone:
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
January 8, 2004 - 4pm Central time zone
Reviewer: Reader from NJ, USA
1) for SQL Server
http://www.dbbalance.com/home.htm
2) for Oracle
http://www.dkgas.com/oradbdiff.htm
How to implement Version management of RDBMS data?
January 8, 2004 - 4pm Central time zone
Reviewer: Mike from NY, USA
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?
Followup January 8, 2004 - 8pm Central time zone:
<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?
January 8, 2004 - 8pm Central time zone
Reviewer: A reader
Just try Change Manager which is part of OEM and third party software like BMC ( http://www.bmc.com
Re:How to implement Version management of RDBMS data?
January 8, 2004 - 8pm Central time zone
Reviewer: A reader
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?
January 8, 2004 - 10pm Central time zone
Reviewer: Mike from NY, USA
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.
How to implement Version management of RDBMS data?
January 9, 2004 - 9am Central time zone
Reviewer: Mike from NY, USA
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
February 10, 2004 - 4pm Central time zone
Reviewer: Gabriel from Montreal, Canada
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.
Followup February 11, 2004 - 8am Central time zone:
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
February 11, 2004 - 10am Central time zone
Reviewer: Gabriel from Montreal, Canada
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,
Followup February 11, 2004 - 12pm Central time zone:
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...
February 11, 2004 - 10am Central time zone
Reviewer: Philippe from Paris
Try oracle Tool free software on
http://www.orafaq.net/links/Products/
Does it do compares
February 11, 2004 - 11am Central time zone
Reviewer: Gabriel from Montreal, Canada
Hello,
Does this OracleTool perform scheduled comapres between a database and a baseline?
Thank you,
Does it do compares
February 11, 2004 - 11am Central time zone
Reviewer: Philippe from Paris
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
February 11, 2004 - 12pm Central time zone
Reviewer: Gabriel from Montreal, Canada
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,
Followup February 11, 2004 - 2pm Central time zone:
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
February 12, 2004 - 11am Central time zone
Reviewer: Gabriel from Montreal, Canada
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,
Followup February 12, 2004 - 12pm Central time zone:
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
February 12, 2004 - 5pm Central time zone
Reviewer: johan graden from sweden
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
February 12, 2004 - 5pm Central time zone
Reviewer: Johan Graden from sweden
use johan<at>graden.com. (replace <at> with @!)
Identifying Data Difference between Schema and Synchronize
November 30, 2005 - 2pm Central time zone
Reviewer: B from US
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
Followup November 30, 2005 - 8pm Central time zone:
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
September 11, 2008 - 4pm Central time zone
Reviewer: Farid from USA
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
April 6, 2009 - 3pm Central time zone
Reviewer: Stefan Pflanz from Germany
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.
|