【无标题】oracle利用dbms_utility.get_hash_value函数比较数据一致性

   oracle通过dbms_utility.get_hash_value函数对对象取HASH值，如果两个对象的hash值相同那么就判断这两个对象的数据是一致的。这种在数据迁移，DG等环境中能用到，可以辅助我们判断数据的一致性检查。
   oracle官方解释dbms_utility.get_hash_value函数是计算字符串的hash值。

DBMS_UTILITY.GET_HASH_VALUE(
name      VARCHAR2,                            --String to be hashed
base      NUMBER,                                 --Base value for the returned hash value at which to start
hash_size NUMBER)                             --Desired size of the hash table
RETURN    NUMBER;

注意：Hash_size为2的幂次方效果最好。
1、创建测试表
SQL> create table aa as select * from dba_tables;

Table created.
2、获取该表的hash值

SQL> col aa format 9999999999999999999999999

SQL> select TO_NUMBER(sum(dbms_utility.get_hash_value(AVG_ROW_LEN || '|' || AVG_SPACE || '|' ||

                                       AVG_SPACE_FREELIST_BLOCKS || '|' ||

                                       BACKED_UP || '|' || BLOCKS || '|' ||

                2    3    4                           BUFFER_POOL || '|' || CACHE || '|' ||

                                       CELL_FLASH_CACHE || '|' || CHAIN_CNT || '|' ||

                                       CLUSTER_NAME || '|' || CLUSTER_OWNER || '|' ||

                       5    6    7                    COMPRESSION || '|' || COMPRESS_FOR || '|' ||

                                       DEGREE || '|' || DEPENDENCIES || '|' ||

                                       DROPPED || '|' || DURATION || '|' ||

                                        8    9   10   EMPTY_BLOCKS || '|' || FLASH_CACHE || '|' ||

                                       FREELISTS || '|' || FREELIST_GROUPS || '|' ||

                                       GLOBAL_STATS || '|' ||

                                       INITIAL_EXTENT || '|' || 11   12   13   INI_TRANS || '|' ||

                                       INSTANCES || '|' || IOT_NAME || '|' ||

                                       IOT_TYPE || '|' || LAST_ANALYZED || '|' ||

                                       LOGGING || '|' || MAX_EXTENTS || '|' 14   15   16   ||

                                       MAX_TRANS || '|' || MIN_EXTENTS || '|' ||

                                       MONITORING || '|' || NESTED || '|' ||

                                       NEXT_EXTENT || '|' ||

                                  17   18   19   20        NUM_FREELIST_BLOCKS || '|' ||

                                       NUM_ROWS || '|' || OWNER || '|' ||

                                       PARTITIONED || '|' || PCT_FREE || '|' ||

                                       PCT_INCREASE || '|' || PCT_ 21   22   23  USED || '|' ||

                                       READ_ONLY || '|' || RESULT_CACHE || '|' ||

                                       ROW_MOVEMENT || '|' || SAMPLE_SIZE || '|' ||

                                       SECONDARY || '|' || SEGMENT_CREATED  24   25   26  || '|' ||

                                       SKIP_CORRUPT || '|' || STATUS || '|' ||

                                       TABLESPACE_NAME || '|' || TABLE_LOCK || '|' ||

                                       TABLE_NAME || '|' || TEMPORARY || '|' ||

  27   28   29   30                                        USER_STATS,

                                       0,

                                       power(2, 30))))  aa

  from SYS.AA; 31   32   33  

 

AA

--------------------------

    1502809347775

注意：对一个表取hash值，需要把该表所有列都计算hash值，然后加起来才是这个表的hash值。所以对列多的表手动处理比较麻烦。利用下面的sql可以方便的处理。

select 'select sum(dbms_utility.get_hash_value(' || column_name_path ||',0,power(2,30)) ) from ' || owner || '.' || table_name || ';' from 
(select owner,table_name,column_name_path,row_number() over(partition by table_name order by table_name, curr_level desc) column_name_path_rank from (select owner,table_name,column_name,rank,level as curr_level,ltrim(sys_connect_by_path(column_name, '||''|''||'),'||''|''||') column_name_path from (select owner,table_name,column_name,row_number() over(partition by table_name order by table_name, column_name) rank from dba_tab_columns where owner= UPPER('&OWNER') and table_name = UPPER('&TABLE_NAME') order by table_name,column_name) connect by table_name = prior table_name and rank - 1 = prior rank)) where column_name_path_rank = 1;