One of the requirements I have lately is to compare table data between two different environments (Development and Test).
The Oracle SQL below compares table1 that has 2 key fields and 3 regular fields.

Note: For the SQL below to work, your password needs to be the same in both environments. If not, then
a connection using the below will not be possible.

FROM table1 tst, table1@dev_database dev

– Compare data from the same table in two different environments
SELECT   tst.fieldkey1, tst.fieldkey2,
         (CASE tst.field1
             WHEN stg.field1
                THEN ‘Match’
             ELSE    ‘field1 in DEV is ‘
                  || dev.field1
                  || ‘. field1 in TEST is ‘
                  || tst.field1
          END
         ) AS field1,
         (CASE tst.field2
             WHEN dev.field2
                THEN ‘Match’
             ELSE    ‘field2 in DEV is ‘
                  || dev.field2
                  || ‘.  field2 in TEST is ‘
                  || tst.field2
          END
         ) AS field2,
         (CASE tst.field3
             WHEN dev.field3
                THEN ‘Match’
             ELSE    ‘field3 in DEV is ‘
                  || dev.field3
                  || ‘.  field3 in TEST is ‘
                  || tst.field3
          END
         ) AS field3
    FROM table1 tst, table1@dev_database dev
   WHERE tst.fieldkey1 = dev.fieldkey1
     AND tst.fieldkey2 = dev.fieldkey2
     AND (   tst.field1 <> dev.field1
          OR tst.field2 <> dev.field2
          OR tst.field3 <> dev.field3
         )
ORDER BY tst.fieldkey1, tst.fieldkey2;