Let your DBA load one of those table to the other database, then use minus operator (Oracle supports it) to compare.
This one gives you those rows exists in 1 but not 2:
select * from table1
minus
select * from table2
This one gives you those rows exists in 2 but not 1:
select * from table2
minus
select * from table1
| [reply]
[d/l]
[select] |
I can think of a few ways of doing this -
Method 1 (row by row comparison, the code is not tested, just a general idea of how the code would look like)
#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::Oracle;
my $db1 = DBI->connect("dbi:Oracle:server=XXXX;database=XXXX", "USER",
+"PASSWD")
or die "Failed to connect to the database!";
my $db2 = DBI->connect("dbi:Oracle:server=XXXX;database=XXXX", "USER",
+"PASSWD")
or die "Failed to connect to the database!";
my $table = "DUMMY"; # name of the table
my $unique_id = "ID"; # unique id to identify the row
my $sth1 = $db1->prepare( "select * from $table" );
my $sth2 = $db2->prepare( "select * from $table where $unique_id=?" );
$sth1->execute();
while (my $row = $sth1->fetchrow_hashref())
{
my $found = 0;
$sth2->execute( $row->{$unique_id} );
while (my $row2 = $sth2->fetchrow_hashref()) {
# compare the row
foreach (keys %$row) {
if ($row{$_} ne $row2{$_}) {
print "DIFF: (ID) $_\n";
break;
}
}
}
}
$sth->finish;
$dbh->disconnect;
Method 2 - DBA way
step 1 - copy both tables into a dummy database, name the tables dummy1 and dummy2
step 2 - copy dummy1 to new table dummy3
step 3 - SQL: delete from dummy1 where ID is found in dummy2
step 4 - delete from dummy3 where ID is found in dummy1
step 5 - delete from dummy2 where ID is found in dummy 3
So in the end, dummy3 holds records in both the original dummy1 and dummy2 tables, and dummy1, dummy2 hold records that do not reconcile.
Method 3 - pg way
Have to say that pg's method is much clever.
| [reply]
[d/l] |
Thank you all for the responses.
I'm trying Roger's method 1.
I'm getting
Global symbol "%row" requires explicit package name at compare.pl line 242.
Global symbol "%row2" requires explicit package name at compare.pl line 242.
I think I can fix that if it was a plain old variable.
Thank you
| [reply] |
sorry that would be this line from Rogers Method 1
if ($row{$_} ne $row2{$_}) {
| [reply] |
Well pg's answer above tells you what sql query to run.
since you are from a programming background it should be quite easy to automate the process in perl.
Install the following modules
-DBI-1.39
-DBD-ORACLE
-DBI-Shell-11.93 (optional).
Once you have these you can write a perl script to connect to the database and run the query given by PG.
If you are interested ,programming the perl DBI is a book that explains how to to this.
SQL "-" is the most appropriate way,though another one can be writing the output of select * from table to a file and using the diff utility.
HTH
<update :as you can see roger beat me to it :)>
| [reply] |