Re: Quick and dirty way to compare table contents

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: ranko.mosic@xxxxxxxxx
  • Date: Wed, 22 Feb 2006 21:23:03 +0100


You have a MD5 function in DBMS_OBFUSCATION_TOOLKIT. The last time I had a look at the docs, it was undocumented. The only snag is that it takes as argument a VARCHAR2 - in other words, you have (in PL/SQL) a 32K limit on the size of the chunk you want to checksum. What you can do in PL/SQL is concatenate everything converted to varchar2 (don't tell me you have LONGs or LOBs) up to 32K, compute the md5 checksum, and build a 'super checksum' out of the computation of all the chunks, and recompute a checksum on it each time you get above 32K. Requires a bit of programming, I am afraid. Don't forget to order by the columns in the PK, because otherwise you can have tables that are logically identical and yet give different checksums.

Hope that helps

Stephane Faroult

Ranko Mosic wrote:

Thanks Luis and Guang.
I personally like spool&diff option but my boss doesn't, so can't use that.
minus option is not an option because tables are in different databases, no link.

On 2/22/06, *Guang Mei* <GMei@xxxxxx <mailto:GMei@xxxxxx>> wrote:

If you only need to compare the contents , you can use
select * from table1 minus select * from table2;
select * from table2 minus select * from table1;

        -----Original Message-----
        *From:* oracle-l-bounce@xxxxxxxxxxxxx
        <mailto:oracle-l-bounce@xxxxxxxxxxxxx>]*On Behalf Of *Ranko Mosic
        *Sent:* Wednesday, February 22, 2006 2:40 PM
        *To:* _oracle_L_list
        *Subject:* Quick and dirty way to compare table contents

Hi list,
I need to quckly compare tables in 2 schemas to verify
contents are identical.
Counting blocks/rows, using tools to compare schemas is not
Something similar to checksum mechanism is requested. Any ideas ?
-- Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr@xxxxxxxxxx <mailto:mosicr@xxxxxxxxxx>


Other related posts: