Oracle获取由select子句定义的数据块的校验和值 [英] Oracle get checksum value for a data chunk defined by a select clause

问题描述

SQL(Oracle)中是否有任何方法可以使我得到类似的信息:

Is there any method in SQL (Oracle) using which I can get something like:

select checksum(select * from table) from table;

推荐答案

您可以为此使用DBMS_SQLHASH.GETHASH.查询结果必须排序，并且不能包含任何LOB，否则结果将不确定.

You can use DBMS_SQLHASH.GETHASH for this. The query results must be ordered and must not contain any LOBs, or the results won't be deterministic.

select dbms_sqlhash.gethash(q'[select * from some_table order by 1,2]', digest_type => 1)
from dual;

摘要类型1 = HASH_MD4、2 = HASH_MD5、3 = HASH_SH1.

Where digest_type 1 = HASH_MD4, 2 = HASH_MD5, 3 = HASH_SH1.

默认情况下，该程序包不授予任何人.要使用它，您需要有人登录为SYS并运行此命令:

That package is not granted to anyone by default. To use it, you'll need someone to logon as SYS and run this:

SQL> grant execute on dbms_sqlhash to <your_user>;

---

必须对查询结果进行排序，如错误17082212:来自不同访问路径的DBMS_SQLHASH不同结果"所述.

---

The query results must be ordered, as described in "Bug 17082212 : DBMS_SQLHASH DIFFERENT RESULTS FROM DIFFERENT ACCESS PATH".

我不确定为什么LOB不起作用，但是可能与ORA_HASH函数不能与LOB很好地起作用有关. 乔纳森·刘易斯(Jonathan Lewis)的这篇文章包含了ORA_HASH返回不同的示例相同LOB数据的结果.最新版本的 SQL语言参考警告不支持LOB.

I'm not sure why LOBs don't work, but it might be related to the way the function ORA_HASH does not work well with LOBs. This Jonathan Lewis article includes some examples of ORA_HASH returning different results for the same LOB data. And recent versions of the SQL Language Reference warn that ORA_HASH does not support LOBs.

这篇关于Oracle获取由select子句定义的数据块的校验和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！