Re: v$sqlarea doesn't show full query

On Feb 6, 1:09 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
> I saw one query doing huge physical reads. But it is not complete as
> it has more characters then 1000. Now I need to tune that but I don't
> have full query, only first 999 characters. We DBAs cann't contact
> developers for this query. it is internally query by the software they
> are using. Is it possible to get full query from database.
>
> Oracle Version : 9.2.0.8
> Platform : Windows XP

You need to use v$sqltext to get the full query text, not v$sqlarea.

select t.sql_text
from v$sqltext t join v$sqlarea a
on a.address = t.address
order by t.address, t.piece;

You can, of course, limit this by other criteria to return only the 'suspect' query text you want to investigate.

David Fitzjarrell Received on Tue Feb 06 2007 - 08:47:09 CST