Difference between two tables

[bang_dba]

Hi folks,

What is the easiest way to find out that the two tables contain the same data ?
And also how can i find out the difference b/n the two tables.

The scenario, i happened to copy a table data into other some time back. I wanted to find out whether there is any diff or is the table data same

[slash]

Hi,
You could write a select with a Minus clause on the 2 tables and find out the difference in data.

Regards
Slash
(MCP)

[anandkl]

One of the simplest way is get the count from both the tables this will tell u the no. of records on both the tables.

regards
anandkl

[bang_dba]

Originally posted by anandkl
One of the simplest way is get the count from both the tables this will tell u the no. of records on both the tables.

regards
anandkl

What if any of the records are only updated ?
or one record is deleted and another is addeded ?


I think Slash's idea sounds good.......

[mrchrispy]

as slash said. if the structure is the same just do a

select * from table1
minus
select * from table2

if not that you'll have to specify the columns you want to check.
chris

[stecal]

Describe what you mean by "difference." Tables can contain the same data (or subset, thereof), but be different in structure. If you don't know if the data set is still the same, then would you know if the structure has been changed (added/dropped a column)?

[Tarry]

That slash stuff will/may not give you everything...It could be anyother way round and should be both ways round....

Enter user-name: appdev@nick817
Enter password:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

appdev@NICK817.TARRY.LOCAL> create table t1
  2  as
  3  select * from all_objects 
  4  /

Table created.

  
appdev@NICK817.TARRY.LOCAL> create table t2
  2  as
  3   select * from t1
  4  /

Table created.

appdev@NICK817.TARRY.LOCAL> delete from t1
  2  where rownum < 10000
  3  /

9999 rows deleted.

appdev@NICK817.TARRY.LOCAL> select count(rownum) from t2
  2  /

COUNT(ROWNUM)
-------------
        25054

appdev@NICK817.TARRY.LOCAL> delete from t2
  2  where rownum <15000
  3  /

14999 rows deleted.

appdev@NICK817.TARRY.LOCAL> select count(*) from t1
  2  /

  COUNT(*)
----------
     15055

appdev@NICK817.TARRY.LOCAL> select count(*) from t2
  2  /

  COUNT(*)
----------
     10055

appdev@NICK817.TARRY.LOCAL> select count(*) from
  2  ((select * from t1
  3  minus
  4  select * from t2)
  5  union all
  6  (select * from t2
  7  minus
  8  select * from t1))--This ensures reqd result
  9  /

  COUNT(*)
----------
      5000

appdev@NICK817.TARRY.LOCAL> select count(*) from
  2  (select * from t1
  3  minus
  4  select * from t2)--While this gives reqd result
  5  /

  COUNT(*)
----------
      5000

appdev@NICK817.TARRY.LOCAL> select count(*) from
  2  (
  3  select * from t2
  4  minus
  5  select * from t1-- This doesn't
  6  )
  7  /

  COUNT(*)
----------
         0

appdev@NICK817.TARRY.LOCAL> drop table t1
  2  /

Table dropped.

appdev@NICK817.TARRY.LOCAL> drop table t2
  2  /

Table dropped.

appdev@NICK817.TARRY.LOCAL>

[bang_dba]

Hi,
Minus returns the distinct rows returned from the first query but not from the second.
So got to be careful when using Minus.....

One more thing which i can think of to find the difference between two tables is Spooling both the tables into a separate text file and use OS command(diff) to findout the difference.
I think this would be the best solution to find out the difference between the table data.