v$sqlarea

[pando]

Hi

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]

[chrisrlong]

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.

HTH,

- Chris

[pando]

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
where b.address=a.sql_address
and username='XXXX'
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]

[chrisrlong]

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.

- Chris

[Edited by chrisrlong on 03-05-2001 at 01:55 PM]

[rsuri]

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.

- Rajeev

[pando]

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

[jdorlon]

Pando,

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..

-John

[pando]

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...

[jdorlon]

Pando,

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...

select sql_text
from sys.v_$sqltext_with_newlines
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.

-John

[jdorlon]

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...