Re: Comparing Tables

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 22 Sep 2012 19:15:26 +0100
Message-ID: <H5-dnX2wT6ymncPNnZ2dnUVZ8sWdnZ2d_at_bt.com>

"The Magnet" <art_at_unsu.com> wrote in message news:cb932f27-ed7f-4e5c-97fa-afd9327e9a15_at_n9g2000yqn.googlegroups.com...
|
| Hi,
|
| I am using this SQL to perform some simple table compares:
|
| data_rec=`sqlplus -s $user/${password} << "EOF"
| set heading off feedback off verify off timing off
| SELECT COUNT(*) FROM
| (SELECT * FROM master_table MINUS SELECT * FROM
| master_table_at_dblink);
| exit
| EOF`
|
|
| However, one of the table a complex data type, with nested tables. I
| cannot use the above method on nested tables. Does anyone know a good
| set of code to compare 2 identical tables, that contain nested tables?
|
| Thanks!

Your SQL is probably deficient. If you want to find differences between the tables you need to do something symmetrical like:

select
from
(select * from table1 minus select * from table2) union all
(select * from table2 minus select * from table1)

You could write your code to take this approach for tables where user_tab_columns shows no "illegal" columns such as LOBs, longs, nested tables etc.
For the remaining tables you could look at the package dbms_rectifier_diff (which exists to sort out replication errors) from dbmsrctf.sql. This MIGHT cover all the options you want but, as you might guess, it's likely to be slow because it has to do row by row comparison.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

Received on Sat Sep 22 2012 - 13:15:26 CDT