Compare Data from the Same Table in two Different Environments

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 dev.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;

 

OK, feel better? Another one for free!

 

 

SELECT   oprid1, run_cntl_id1

    FROM (SELECT *

            FROM (SELECT oprid AS oprid1, run_cntl_id AS run_cntl_id1

                    FROM ps_fas_run_ap735

                  UNION ALL

                  SELECT oprid AS oprid1, run_cntl_id AS run_cntl_id1

                   FROM ps_fas_run_ap735@fsdev) a)

GROUP BY oprid1, run_cntl_id1

 HAVING COUNT (*) = 1

转载于:https://www.cnblogs.com/GoDevil/archive/2008/08/08/1263801.html