Trouble locating current SQL in v$sqlarea for a session

Back in 1995 I wrote a couple of queries that would show me the current SQL being executed by a particular session. Whenever the status in v$session was 'ACTIVE' I could use these scripts to see what the session was doing. These scripts worked pretty well in 7.1.3, 7.1.6, and 7.2.3, but recently I noticed that the queries would sometimes return 'no rows' for sessions with a status of 'ACTIVE' under version 7.3.3.5. I have since determined that the v$session sql_hash_value will be negative when this occurs.

Who out there knows how to handle this so that the query will once again work all the time? Here is one of the queries, the other uses v$sqltext. I haven't had much call to use these scripts recently, but when I noticed the problem I started to look into it, but I have a little too much to do right now and I haven't tried to look for an underlying x$ table.

set echo off
rem
rem filename: sql_user.sql
rem SQL*Plus script to display the sql being executed for a particular rem Oracle session.
rem
rem 11/27/95 s3527 m d powell new script rem
set verify off
select sid, username, command, lockwait, status,

          osuser, sql_text
from v$session s, v$sqlarea q
where s.sql_hash_value = q.hash_value

and     s.sql_address       = q.address
and     s.sid                     = &usrsid

I have verified that the sql_hash_value goes negative sometimes under Oracle version 7.3.3.5 on both Pyramid Dcosx and Sequent Dynix machines and when it does the queries do not find the SQL. Received on Sun Nov 29 1998 - 23:42:25 CST