how to get full version of sql statement
I m using statspack for tuning sql statements.
here I can get bed sql queries which I have to tune,but I get only a partial version of sql statement and hash value.Now if I want to extract full version from hash value how can i get?Dont say v$sql or v$sqlarea,because here also i m getting only a part version as it stores only 1000 character (varchar2(1000))
how can i get full version of this sql statement so that i can tune the same..
You cannot from statspack report .
But you can get them if the queries are there still in the library cache then look at v$sqltext_with_newlines.
You can also have a look at the stats$sqltext in perfstat schema
Last edited by hrishy; 08-12-2005 at 02:59 AM.
Another way of getting the full query is that pefrstat schema should have a package sp901.Once you have that you can run the following query that will give you the full SQL statement-
select perfstat.sp901.getSQLText ( hash_value , 50 ) "SQL Statement" from dual;
Hash value you get it from Statspack Report.
Click Here to Expand Forum to Full Width