The reviewer just above me recommended Tom Kyte's GROUP BY method for comparing tables. I wrote a package that generates the SQL for comparing two tables using this method. See
https://stewashton.wordpress.com/2015/01/21/stew_compare_sync-introducing-the-package/ Example using SCOTT.EMP:
select compare_sync.compare_sql('SCOTT', 'EMP', 'SCOTT.EMP') from dual;
select
"EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO",
sum(OLD_CNT) OLD_CNT, sum(NEW_CNT) NEW_CNT
FROM (
select
"EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO",
1 OLD_CNT, 0 NEW_CNT
from SCOTT.EMP O
union all
select
"EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO",
0 OLD_CNT, 1 NEW_CNT
from SCOTT.EMP N
)
group by
"EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO"
having sum(OLD_CNT) != sum(NEW_CNT)
order by 1, NEW_CNT;
To generate the SQL queries for every table in a schema:
select compare_sync.compare_sql('SCOTT', table_name, 'SCOTT.'||table_name)
from all_tables where owner = 'SCOTT';
There is an option to limit the comparison to specific columns if you need it.