Re: Quick and dirty way to compare table contents

Ranko,

     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_at_ph.com <mailto:GMei_at_ph.com>> 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;
>
> Guang
>
> -----Original Message-----
> *From:* oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>]*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
> possible.
> 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_at_rogers.com <mailto:mosicr_at_rogers.com>
> http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
> <http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html>
>
>

--
http://www.freelists.org/webpage/oracle-l