SQL stmt in SGA. Help!

[Chakravarthy KM Nalamotu]

Hi Oracle Users,
Could some one let me know how to view the entire
SQL text residing in the SGA. SQL_TEXT field in
the V$SQLAREA view is 1000 char long. What if there
is a SQL statement that is longer than that, let's say
5000 char long. How would I view such statments.
Thank you for your replies.
Kittu.

[Mary Travis]

Try this in SQL*Plus, given to me by most excellent Oracle DBA, Ping
Zhang

ttitle CENTER "Current active SQL statements" skip 1 -
CENTER "=========================" skip 1
set pagesize 30
set linesize 80
set feedback off
col sid format 999
col username format A8
break on sid on username skip 1
select a.sid, a.username, b.sql_text from v$session a,
v$sqltext b
where a.sql_address = b.address and a.sql_hash_value=b.hash_value
and a.username is not null and a.status = 'ACTIVE' and a.user# != uid
order by a.sid, b.piece
/
set feedback on
set pagesize 14

Any typos are my own and not Ping's!
Hope this helps.

Mary Travis
BellSouth Telecommunications
Mary Travis
BellSouth Telecommunications
email: Mary.B...@bridge.bellsouth.com

[Chakravarthy KM Nalamotu]

My question still holds good...
In your SELECT there is a sql_text field which
is 1000 long VARCHAR2 field. What happens to those
statements that are longer than 1000 chars?
What if a user uses a SQL statement that is 5000 chars
long. Does it get stored in the SGA? or will it be
truncated?
Thanks,