how to get full version of sql statement

[rgopani]

Hi,
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..

[hrishy]

Hi

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

regards
Hrishy

[bonny]

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.

Bonny