Re: Comparing Tables
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 26 Sep 2012 08:02:32 -0700 (PDT)
Message-ID: <b253de06-3c2d-4a8e-8067-92e5cecb7f3d_at_googlegroups.com>
On Saturday, September 22, 2012 2:15:24 PM UTC-4, Jonathan Lewis wrote:
> "The Magnet" <art_at_unsu.com> wrote in message news:cb932f27-ed7f-4e5c-97fa-afd9327e9a15@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
Date: Wed, 26 Sep 2012 08:02:32 -0700 (PDT)
Message-ID: <b253de06-3c2d-4a8e-8067-92e5cecb7f3d_at_googlegroups.com>
On Saturday, September 22, 2012 2:15:24 PM UTC-4, Jonathan Lewis wrote:
> "The Magnet" <art_at_unsu.com> wrote in message news:cb932f27-ed7f-4e5c-97fa-afd9327e9a15@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
Skipping non-supported data type coliumns is exactly what Oracle recommends in the PL/SQL Packages and Types manual for the new with 11g dbms_comparison package which is designed to compare objects between databases.
If there are several tables to be compared then perhaps the package can be used form some of them and customer code for the tables with unsupported data types if the column cannot be left out of the comparison.
HTH -- Mark D Powell -- Received on Wed Sep 26 2012 - 10:02:32 CDT