Oracle Database 19c -- Compute a Checksum for a Table

Problem

You need calculate a checksum value for a table for checking integrity and consistency of table data as part of migration or any other use cases

 

Solution

Use the below script to generate a hash value for a table.

 

Prerequisite for the script is the EXECUTE privilege on the DBMS_CRYPTO package to properly handle the LOB type columns.

 

set serveroutput on
declare
    count_value number;
    hash_value  number;
    procedure checksum(
        p_owner         IN dba_tables.owner%type,
        p_tablename     IN dba_tables.table_name%type,
        p_count_value   OUT number,
        p_hash_value    OUT number)
    as
        stmt        clob;
        col_list    clob;
        hash_value  number;
        count_value number;
        HASH_SH512  number := 6;
    begin   
        for c in (select column_name, column_id, data_type
                    from dba_tab_columns
                    where table_name = p_tablename and owner=p_owner
                    order by column_id)
        loop
            col_list := col_list || ' + case when <column_name> is not null then <hash_function> else <column_id> end ';
            col_list := replace(col_list, '<column_name>', c.column_name);
            col_list := replace(col_list, '<hash_function>',
                case
                    when c.data_type in ('CLOB','NCLOB','BLOB')
                        then 'ora_hash(dbms_crypto.hash('||c.column_name||', '|| HASH_SH512 ||'))'
                    else 'ora_hash('||c.column_name||')'
                end
                );
            col_list := replace(col_list, '<column_id>', c.column_id);
        end loop;
        
        stmt := 'select /*+ parallel */ count(*) count_value, sum( 0 <col_list> ) hash_value from <table_owner>.<table_name>';
        stmt := replace(stmt, '<col_list>',    col_list);
        stmt := replace(stmt, '<table_name>',  p_tablename);
        stmt := replace(stmt, '<table_owner>', p_owner);
        
        execute immediate stmt into p_count_value, p_hash_value;
    end;
begin
    checksum('<owner>','<table_name>', count_value, hash_value);
    dbms_output.put_line(hash_value);
end;