How to generate a single MD5 hash out of a complete given table [duplicate]

Question

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

select checksum(select * from table) from table;

Answer 1

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;

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

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>;

---

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

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.