compare results from 2 separate databases

[yogione]

Can I do the following from plsql developer.
I have login for both databases. thanks.

select cust_name from oracle@chicago.world
where cust_name
not in ( select cust_name from oracle@newyork.world )

 

[IvanZ]

For that select to work DB "chicago.world" should have database link to DB "newyork.world" named "newyork.world".

Or you can fetch both full results to the client, then insert it to the tables in one database and do the statement.

 

[yogione]

Thanks.
I need a way to do everything in the client - that is plsql developer.
No privileges to create tables or database links in the instance / schema. Currently, I spool results to 2 separate text files and have a small Perl script to tell me the diffs.

 

[Gustavo]

If there aren't too many rows there, you could try this:

select cust_name from oracle@chicago.world
minus
select cust_name from oracle@newyork.world;

 

[Hilarion]

This comparison is asymmetric - will not show rows that are in NewYork DB but are not in Chicago DB. This also requires db-links, which yogione does not have.

I'd suggest trying "Compare Table Data" function of PL/SQL Developer ("Tools" menu, 4th option from the bottom, at least in version 10.0.5), which should allow comparing tables between current connection and another one given by "Target Session ...".