Does anyone know how to show sql_text in v$sqlarea fully? I have tried sqlplus and toad and I always get truncated results :0
And I dont get all the SQL statements from one user if I query v$sqlarea...
[Edited by pando on 03-05-2001 at 12:25 PM]
Unfortunately, that's because v$SQLArea only holds truncated text. If you want the full test, you will need to use v$SQLText, which holds the full statements, but broken into little chunks. I have a query to join them lying around somewhere. I'll post it if I find it.
Also, this view is of a cache, basically, and statements get cached out. This would most likely explain why you don't get all the SQL you would like to from it.
Well the strange thing is that with Quest Spotlight for Oracle I can see all the sql statement issued by the user but from sqlplus if I issue
select username, buffer_gets, substr(sql_text, 1, 4000)
from v$sqlarea b, v$session a
order by buffer_gets desc
I only see about 8 queries when there are about 30 (and all fully)!
And i can not find out which table is Spotlight quering to get those results :o
[Edited by pando on 03-05-2001 at 12:45 PM]
You could always SELECT * FROM V$SQLAREA WHERE SQL_TEXT LIKE '%V$%' to see all the selects against the dynamic performance views. This should allow you to find the query that Spotlight is using.
As for the query you are using, it looks a little suspect. There is only one entry per connection in v$session, hence only one SQL_Address. Why would you expect this singular SQL_Address to point to all the statements this session has run? This just doesn't seem to make sense, but it could just be me :). I would expect that this would point to the current or latest statement that the session ran.
[Edited by chrisrlong on 03-05-2001 at 01:55 PM]
try this --
SELECT T.SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE S.SID = 100 AND S.SQL_ADDRESS=T.ADDRESS ORDER BY T.PIECE
If you don't know the SID, you will have to use the username etc. to filter.
I think sql_address and address corresponds to each other so hash_value and sql_hash_value because i queried both views v$session and v$sqlarea and checked for those values and the results of the query I issued made sense since all of them were issued but then again a lot were missing!
Rsuri I still cant see all te sql_text with that query :o
If you match the rows to v$sqltext_with_newlines instead of v$sql_text then the sql text will be formatted as the user entered it rather than all mashed together..
Well I am trying to find the offensive SQL statements that's why I am querying v$sqlarea, v$sqltext_with_newlines I used it to find the last statement issued by an user...
take the value out of the ADDRESS column out of the row in V$SQL you are interested in and use it in this query...
where address = :addr
ORDER BY piece
forget the HASH_ columns. they don't always match up so well. I don't understand why, but they don't.
EZSQL (www.ezsql.net) has a really easy interface for finding the bad queries... then you can right-click any of the queries to see the full text, or get an explain plan...
Click Here to Expand Forum to Full Width