v$sqltext not showing all SQL statements

From: Laith Suheimat <laith1_at_my-deja.com>
Date: Fri, 06 Aug 1999 14:29:46 GMT
Message-ID: <7oergc$88i$1@nnrp1.deja.com>

Hello,

I'm trying to get all the SQL for current user processes using the following SQL:

select s.username,
       s.sid,
       p.spid
       s.terminal
       s.osuser
       nvl(s.machine,'Undefined')  machine
       substr(s.program, 1, 64)  prog
       q.sql_text
       q.piece
from  v$sqltext q,
      v$session s,
      v$process p
where s.terminal is not null

and q.address(+) = s.sql_address
and s.paddr = p.addr

order by s.username,
         q.address,
         q.piece

It works in most cases, but I am finding that for certain queries (eg. select * from user_tables), although the above SQL returns session and process info (because of the outer join), it doesn't return the SQL.

This occurs even when I rerun the query or change it slightly (e.g. add where clause, order by, etc.). If I change it to an update, insert or commit however, that SQL is returned from v$sqlarea.

If I look in v$open_cursor I see the missing SQL. Unfortunately, v$open_cursor only contains the first 60 characters of the parsed SQL.

This is on Oracle 7.3.3.0.0 on Windows NT 4.0 Wkstn sp 4.

I would be interested to know if anyone else has had this problem - perhaps it is a feature of v$sqltext?

Regards,

Laith Suheimat

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Aug 06 1999 - 09:29:46 CDT