ORA_HASH and LOBs …. not nice partners

Posted by

Consider the following simple setup

SQL> create table t ( x int, b blob );

Table created.

SQL> declare
  2    bin blob;
  3  begin
  4    insert into t values (1, empty_blob())
  5    returning b into bin;
  6
  7    dbms_lob.writeappend(bin,10000,utl_raw.cast_to_raw(rpad('x',10000,'x')));
  8    commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 3856858313

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 3856858313

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 3856858313

That all seems fine…but thats because I ran the ORA_HASH commands quickly one after the other… Now that 30 seconds has passed (as I type this into the blog)…lets run the same ORA_HASH again

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 1078420585

It changes ! In fact, one most platforms it appears to change every 3 seconds, so presumably somewhere in there its related to one the internal Oracle timers in some way.

Bottom line – you can’t use ORA_HASH on LOB’s. (There is also some information in Metalink note 427803.1)

One comment

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.