Here’s a quick and dirty method to compare the contents of a couple tables:

See all the rows in hr.employees which do not appear in oe.employees

SQL> select * from hr.employees
2 minus
3 select * from oe.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
210 Groucho Marx
gm@lifeaftercoffee.com 26-JUL-07 ST_MAN

And the converse can be done by switching the order of the tables:

See all the rows in oe.employees which do not appear in hr.employees

SQL> select * from oe.employees
2 minus
3 select * from hr.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
207 Jon Emmons
jon@lifeaftercoffee.com 26-JUL-07 AD_VP

208 Gwen Emmons
gwen@lifeaftercoffee.com 26-JUL-07 AD_VP

This can be very useful if you need to, say, determine what has changed between two different databases. I have been using this over a database link recently to compare tables in a development instance to the tables in a production instance.

The caveats: The two tables must have the exact same column layout. If they are close but not exact you should still be able to make this work by specifying all the fields which are the same instead of ‘*’.

If you want to exclude a field (like an activity date) you will need to specify all the fields you want to include from both tables. The types and order of the fields must be identical between the two select statements.

I describe this as “quick and dirty” because it is. You should not use a query like this in a production database without first looking at how it will affect your database. This will cause full table scans and could kill performance.

oracle, database, dba, database administration, sql