Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







dbms_sqlhash tips

Oracle Database Tips by Donald BurlesonFebruary 11, 2015


You can use the dbms_sqlhash procedure to see if the result set for a SQL statements has changed and dbms_sqlhash can also be used to check data integrity.

The dbms_sqlhash procedure is created by running dbmsobtk.sql at $ORACLE_HOME/rdbms/admin/dbmsobtk.sql.

   sqltext     in varchar2,                
   digest_type in binary_integer,          
   chunk_size  in number default 134217728)
return raw;

The Oracle 10g security guide notes the arguments to dbms_sqlhash:

sqltext The SQL statement whose result is hashed
digest_type Hash algorithm used: HASH_MD4, HASH_MD5 or HASH_SH1
chunk_size Size of the result chunk when getting the hash

When the result set size is large, the GETHASH function will break it into chunks having a size equal to chunk_size. It will generate the hash for each chunk and then use hash chaining to calculate the final hash. The default chunk_size is 128 MB.

The docs also note the general usage for dbms_sqlhash:

'the DBMS_SQLHASH package can check data integrity by making use of hash algorithms. It provides an interface to generate the hash value of the result set returned by a SQL query.

Hash values are like data fingerprints and are used to ensure data integrity. DBMS_SQLHASH provides support for several industry standard hashing algorithms, including MD4, MD5, and SHA-1 cryptographic hashes . .

DBMS_SQLHASH includes the GETHASH function that is used to retrieve the hash value of a query result set. The GETHASH function runs one of the supported cryptographic hash algorithms against the result set of the SQL statement to arrive at a hash value.

You can compare hash values to check whether data has been altered. For example, before storing data, Laura runs the DBMS_SQLHASH.GETHASH function against the SQL statement to create a hash value of the SQL result set.

When she retrieves the stored data at a later date, she reruns the hash function against the SQL statement using the same algorithm. If the second hash value is identical to the first one, then data has not been altered. Any modification to the result set data would cause the hash value to be different.?

Pete Finnigan notes that dbms_sqlhash can also check data integrity, to "test the integrity of dictionary objects in a similar fashion to some of the commercial database scanners":

?[The dbms_sqlhash package] is an interesting package that allows the use of cryptographic hashes such as HASH_MD4, HASH_MD5, or HASH_SH1 (From DBMS_CRYPTO) to hash the result set of a SQL statement to allow the checking of data integrity.

This allows data to be checked to see if it has been changed.

The package with the function GETHASH can also be used to test the integrity of dictionary objects in a similar fashion to some of the commercial database scanners that are available.

The package can of course be used to select the source of packages, triggers, views and more and hashes can be calculated and stored for later comparison.?


If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.