how to get full version of sql statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: how to get full version of sql statement

  1. #1
    Join Date
    Jun 2005
    Location
    India
    Posts
    14

    Arrow how to get full version of sql statement

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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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
    Last edited by hrishy; 08-12-2005 at 02:59 AM.

  3. #3
    Join Date
    Aug 2002
    Posts
    40
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width