I am querying V$SQL and the results of SQL_TEXT are getting truncated. Is there an easy way to remedy this, like a SET command?
Thanks
-mcslain
Printable View
I am querying V$SQL and the results of SQL_TEXT are getting truncated. Is there an easy way to remedy this, like a SET command?
Thanks
-mcslain
usually the text will be displayed in inverted format....
get all "pieces" together ordered by "piece" then parse it
also "set long" env. variable to a higher value.
Thanks,
v$sql does NOT show you the entire SQL statements, you need to query v$sqltext_with_newlines until 9i, in 10g you can query sql_fulltext in v$sqlstats (for this one you have to use set long to a hugh value)
agree with you Pando. Poster can use sql_fulltext instead of sql_text even from v$sql.
Mcslain - you can get the complete sql from v$sql.sql_fulltext or v$sqltext.sql_text (order by piece) or from v$sqlstats.sql_fulltext. Advantage with v$sqlstats is you can find the SQL even after the cursor aged out of shared pool.
SQL> set long 50000
SQL> set line 100
SQL> select sql_fulltext from v$sql where hash_value=xxxxxxxxxx; or
SQL> select sql_text from v$sqltext where hash_value=xxxxxxxx order by piece; or
SQL> select sql_fulltext from v$sqlstats where plan_hash_value=xxxxxxxxxx;
Thanks,