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]
03-05-2001, 12:31 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.
03-05-2001, 12:41 PM
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]
03-05-2001, 01:48 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]
03-05-2001, 01:48 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.
03-05-2001, 01:57 PM
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
03-05-2001, 04:14 PM
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..
03-05-2001, 04:18 PM
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...
03-05-2001, 04:21 PM
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.
03-05-2001, 04:24 PM
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...