Re: v$sqltext not showing all SQL statements

It's a problem with signed and unsigned 32-bit integers. In some of the views the hash value is signed, in others it is unsigned, so a hash value that sets the 32nd bit will sometimes drop out of site as you join two views.

There is a note about it on my web site under:

    internals -> losing sql text

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Laith Suheimat wrote in message <7oergc$88i$1_at_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:39:51 CDT