Re: Decrease size of field sql_text in table v$sqlarea

A copy of this was sent to "Bjørnar Nilsen" <bni_at_start.no> (if that email address didn't require changing) On Mon, 28 May 2001 01:38:34 +0200, you wrote:

>We want to trace sql-statement executed on our oracle 7.3.4 database by
>examination of the sql_text - field in the v$sqlarea - table. Unfortunately
>some of the statements exceeds the size of the sql_text - field
>(varchar2(1000)). Is it possible to increase the size, and - because this is
>a system table, are there any "side-effects".
>
>Thanks in advance
>

Well, the V$ tables are really tables at all. they are "views" of data structures. you would not have any chance of increasing the columns

The view you want is v$SQLTEXT_WITH_NEWLINES

 Name                            Null?    Type
 ------------------------------- -------- ----
 ADDRESS                                  RAW(4)
 HASH_VALUE                               NUMBER
 COMMAND_TYPE                             NUMBER
 PIECE                                    NUMBER
 SQL_TEXT                                 VARCHAR2(64)

it has the entire query in there. order by piece for a given address to get the entire query.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp