Does anyone know how to get the full sql statement from the sql_text column of v$sqlarea. The column type is varchar2(1000) but the documentation says it will only show 80 characters.
Any help would be much appreciated.
Printable View
Does anyone know how to get the full sql statement from the sql_text column of v$sqlarea. The column type is varchar2(1000) but the documentation says it will only show 80 characters.
Any help would be much appreciated.
v$sqltext and V$SQLTEXT_WITH_NEWLINES.
Try this one:
this
/*will break the query into segments of 64 bytes and there will be multiple rows
/*per statement, depending on length of query*/
select disk_reads, executions, disk_reads/executions, b.sql_text, first_load_time
from v$sqlarea a, v$sqltext b
where a.address=b.address and disk_reads/decode(executions,0,1,executions) > 100
and executions > 0
order by disk_reads/decode(executions,0,1,executions) desc,b.address, b.piece;