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)
Oracle 10g security guide notes the arguments to dbms_sqlhash:
statement whose result is hashed
used: HASH_MD4, HASH_MD5 or HASH_SH1
|Size of the
result chunk when getting the hash
When the result set size is large, the
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
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
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
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.