Oracle debugging technique for row difference

I a开发者_JAVA技巧m running a query on production and it is say returning me 500 rows and I have the same copy on my dev and the query is returning only 497 rows.

What approach or steps can be taken to compare the results?

Is there a tool?

Assuming there is a database link between the two databases and that the data being returned matches for most of the rows in question, you could use a MINUS operation. Something like

SELECT list_of_columns
  FROM some_table
 WHERE some_criteria
MINUS
SELECT list_of_columns
  FROM some_table@db_link_to_dev
 WHERE some_criteria

will show you the rows that are returned by the first query that do not have an identical match in the rows returned from the second query. Ideally, that would show you just the three extra rows. But it may return additional rows if the data returned for some columns is different in the two environments.

If you can save the result sets in flat files, then you could also grab a copy of the free and open source WinMerge utility and compare the results. This eliminates the need to create any additional database objects. Plus, WinMerge is a valuable tool in it's own right.

EDIT: Assuming windows, of course. A *nix solution could be diff.