oracle哈希检查数据一致性,Oracle利用HASH值比较数据一致性

通过dbms_utility.get_hash_value对对象取HASH值，如果两个对象的hash值相同那么就判断这两个对象的数据是一直的。这种在数据迁移，DG等环境中能用到，可以辅助我们判断数据的一致性。

https://www.cndba.cn/Expect-le/article/1907

1

dbms_utility.get_hash_value说明

官网的解释：这个函数是计算字符串的hash值。

https://www.cndba.cn/Expect-le/article/1907

语法：

DBMS_UTILITY.GET_HASH_VALUE (

name VARCHAR2,

base NUMBER,

hash_size NUMBER)

RETURN NUMBER;

https://www.cndba.cn/Expect-le/article/1907

Parameter

Description

name

String to be hashed.

base

Base value for the returned hash value at which to start

注意：Hash_size为2的幂次方效果最好。

2

例子

2.1

创建一个表

SQL> create table aa as select * from dba_tables;

Table created.

2.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('SYS') and table_name = UPPER('AA') order by table_name,

column_name)

connect by table_name = prior table_name

and rank - 1 = prior rank))

where column_name_path_rank = 1;

https://www.cndba.cn/Expect-le/article/1907

2.3

删除表中几条数据

SQL> delete aa where rownum <=10;

10 rows deleted.

SQL> commit;

Commit complete.

2.4

再次查看该表hash值

可以看到hash值已经变了

https://www.cndba.cn/Expect-le/article/1907

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

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

1496783041740

https://www.cndba.cn/Expect-le/article/1907

注意：该函数对列类型是LONG,LOB,CLOB等无法得到准确的hash值。

版权声明：本文为博主原创文章，未经博主允许不得转载。

hash值 数据一致