Post date: 04-Nov-2009 08:49:36
Doc ID : 437304.1
V$SQL or V$SQLAREA contains the column SQL_FULLTEXT which contains the full text for the SQL statement, stored in a CLOB column.
In order to retrieve the full SQL text (up to 4000 characters) :
set linesize 132 pagesize 999
column sql_fulltext format a60 word_wrap
break on sql_text skip 1
select
replace(translate(sql_fulltext,'0123456789','999999999'),'9','')
SQL_FULLTEXT
from v$sql
where sql_text like '%TEXT IDENTIFIER%'
group by replace(translate(sql_fulltext,'0123456789','999999999'),'9','')
/
Â
Example output:
SQL_FULLTEXT
------------------------------------------------------------
select /*aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
... query text ...
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa */ from b
NOTE:260942.1 - Display Execution plans from Statement's in V$SQL_PLAN
NOTE:62143.1 - Understanding and Tuning the Shared Pool