I am comparing tables in two different schemas. Since I am doing the same thing over and over again I created a small script which might be useful to you too.

ACCEPT db1 CHAR prompt ‘owner 1 :’
ACCEPT db2 CHAR prompt ‘owner 2 :’
ACCEPT tabname CHAR prompt ‘tablename :’
PROMPT
PROMPT &db1..&tabname minus &db2..&tabname
SELECT * FROM &db1..&tabname
MINUS
SELECT * FROM &db2..&tabname;
PROMPT
PROMPT &db2..&tabname minus &db1..&tabname
SELECT * FROM &db2..&tabname
MINUS
SELECT * FROM &db1..&tabname;
PROMPT
PROMPT count(*) &db1..&tabname
SELECT COUNT(*) FROM &db1..&tabname;
PROMPT
PROMPT count(*) &db2..&tabname
SELECT COUNT(*) FROM &db2..&tabname;
PROMPT
PROMPT count(*) union
SELECT COUNT(*) FROM
(
SELECT * FROM &db1..&tabname
UNION
SELECT * FROM &db2..&tabname
);
PROMPT
PROMPT count(*) union all
SELECT COUNT(*) FROM
(
SELECT * FROM &db1..&tabname
UNION ALL
SELECT * FROM &db2..&tabname
);

First the script asks for the schemas in which the table reside. Then it asks for the tablename you want to compare. After you have entered this information, the script performs a couple of simple sql statements.

1. schema1.table minus schema2.table
2. schema2.table minus schema1.table
3. count of schema1.table
4. count of schema2.table
5. count of (schema1.table union schema2.table)
6. count of (schema1.table union all schema2.table)

The difference between union and union all is that union removes all duplicates from the result set, whereas union all just unions all records from both tables.

Notice the double dot (..) notation in &db1..&tabname. If you don’t use this notation, then Oracle seems to forget the dot between the schemaname and the tablename.