Re: Oracle table compare (data not file) functionality required ...

Hi,

I think you do not need any software in this situation. First of all copy remote tables via db link into the local database. This can be done via "insert ... select" approach:

INSERT INTO <remote table>(<remote table columns>)  SELECT <remote table columns>
 FROM <remote table>@<db link>;

You can use COPY command of SQL Plus or my freeware Table Copy utility if you do not want to use db link:

http://protasov.simplenet.com

Then use the following pseudo code to synchronize:

UPDATE <local table> X
 SET ( <local table columns> )=

     (SELECT
       <remote table columns>
      FROM <remote table> Y
      WHERE
       X.<primary_key_column1>=Y.<primary_key_column1>
       ...
       X.<primary_key_columnN>=Y.<primary_key_columnN>)
 WHERE EXISTS
     (SELECT *
      FROM <remote table> Y
      WHERE
       X.<primary_key_column1>=Y.<primary_key_column1>
       ...
       X.<primary_key_columnN>=Y.<primary_key_columnN>);

INSERT INTO <local table>(<local table columns>)  SELECT <remote table columns>
 FROM <remote table>
 MINUS
 SELECT <local table columns>
 FROM <local table>;

The last insert can be easily rewritten without MINUS using NOT EXISTS. In your situation it will be more effective.

I use the above statements in my Schema Comparison utility:

http://protasov.simplenet.com

It can synchronize two tables in this way. I believe that you do not have LONG or LONG RAW columns in your tables. Otherwise the task becomes much more serious.

Andrew Protasov

> I am looking for a utility which can identify the data differences between 2
> identical tables. The differences would be identified based on the primary
> key of the table. Ideally the utility will output data to a transaction
> file, detailing the change - INSERT, DELETE or UPDATE. All data for non key
> columns must be included for the INSERT and UPDATE.
>
> It would be really good if the utility went a further step and actually
> applied the changes to the destination table.
>
> Note that the destination table is on a oil rig in the Timor Sea with a
> satellite link. Large data bases are involved so minimum data transfer is a
> must. Replication is not a solution as the source table is truncated on a
> daily basis.
>
> Thanks in Advance. RM.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>
Received on Wed Nov 25 1998 - 04:37:42 CST