CNDBA社区
通过dbms_utility.get_hash_value对对象取HASH值，如果两个对象的hash值相同那么就判断这两个对象的数据是一直的。这种在数据迁移，DG等环境中能用到，可以辅助我们判断数据的一致性。
1 dbms_utility.get_hash_value说明
官网的解释：这个函数是计算字符串的hash值。
语法：
DBMS_UTILITY.GET_HASH_VALUE (
name VARCHAR2,
base NUMBER,
hash_size NUMBER)
RETURN NUMBER;| Parameter |
Description |
| name |
String to be hashed. |
| base |
Base value for the returned hash value at which to start |
| hash_size |
Desired size of the hash table |
注意：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可以方便的处理。
输入用户名称和表名，然后执行得打的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;2.3 删除表中几条数据
SQL> delete aa where rownum <=10;
10 rows deleted.
SQL> commit;
Commit complete.2.4 再次查看该表hash值
可以看到hash值已经变了
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
注意：该函数对列类型是LONG,LOB,CLOB等无法得到准确的hash值。
版权声明：本文为博主原创文章，未经博主允许不得转载。
hash值 数据一致
