How to get full query for end user sessions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to get full query for end user sessions

  1. #1
    Join Date
    Feb 2009
    Posts
    49

    How to get full query for end user sessions

    Hi ,

    I want to get the full sql stmt running by end user.I tried
    v$sqltext;
    v$sqlarea;
    v$sql;
    v$sqltext_with_newlines;

    But none of the views is giving me fill sql statement only partial....Can u pls help..?

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    V$SQLTEXT should provide entire SQL. BTW what's your Oracle Version?
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Apr 2008
    Location
    Bangalore
    Posts
    96
    try changing the column format

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Mohith123 View Post
    try changing the column format
    Not sure what you mean.

    As stated by Malay v$sqltext provides the info poster is looking for, just take into consideration each query is described in as many v$sqltext rows as needed; don't forget to sort by piece.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Apr 2008
    Location
    Bangalore
    Posts
    96
    column format a500;

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Mohith123 View Post
    column format a500;
    Why?

    Code:
    SQL> desc v$sqltext
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ADDRESS                                            RAW(8)
     HASH_VALUE                                         NUMBER
     SQL_ID                                             VARCHAR2(13)
     COMMAND_TYPE                                       NUMBER
     PIECE                                              NUMBER
     SQL_TEXT                                           VARCHAR2(64)
    
    SQL>
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Apr 2008
    Location
    Bangalore
    Posts
    96
    SQL> col SQL_TEXT format a50
    SQL> select SQL_TEXT from v$sqltext where SQL_ID='6hwjmjgrpsuaa';

    SQL_TEXT
    --------------------------------------------------
    , req_reason
    nqueue_statistics where total_req# != 0 order
    by eq_type

    #, succ_req#, failed_req#, cum_wait_time, event
    # from v$e

    :instance_number, eq_type, req_reason, total_re
    q#, total_wait

    led_req#, cum_wait_time, event#) select :sn

    SQL_TEXT
    --------------------------------------------------
    ap_id, :dbid,

    eq_type, req_reason, total_req#, total_wait#,
    succ_req#, fai

    insert into wrh$_enqueue_stat (snap_id, dbid, in
    stance_number,


    7 rows selected.

    SQL> col SQL_TEXT format a10000
    SQL> select SQL_TEXT from v$sqltext where SQL_ID='6hwjmjgrpsuaa';

    SQL_TEXT
    --------------------------------------------------------------------------------
    , req_reason
    nqueue_statistics where total_req# != 0 order by eq_type
    #, succ_req#, failed_req#, cum_wait_time, event# from v$e
    :instance_number, eq_type, req_reason, total_req#, total_wait
    led_req#, cum_wait_time, event#) select :snap_id, :dbid,
    eq_type, req_reason, total_req#, total_wait#, succ_req#, fai
    insert into wrh$_enqueue_stat (snap_id, dbid, instance_number,

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Mohith123 View Post
    SQL> col SQL_TEXT format a10000
    SQL> select SQL_TEXT from v$sqltext where SQL_ID='6hwjmjgrpsuaa';

    SQL_TEXT
    --------------------------------------------------------------------------------
    , req_reason
    nqueue_statistics where total_req# != 0 order by eq_type
    #, succ_req#, failed_req#, cum_wait_time, event# from v$e
    :instance_number, eq_type, req_reason, total_req#, total_wait
    led_req#, cum_wait_time, event#) select :snap_id, :dbid,
    eq_type, req_reason, total_req#, total_wait#, succ_req#, fai
    insert into wrh$_enqueue_stat (snap_id, dbid, instance_number,
    I see, very useful huh?

    Can't you see don't matter if you format the column a1Trillion the column would always hold no more than 64 chars?

    Why don't you do it the easy -right way and stop confusing the poster?

    Code:
    select   sql_text 
    from     v$sqltext 
    where    sql_id='???'
    order by piece;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Apr 2008
    Location
    Bangalore
    Posts
    96
    thanks for that...sorry if i created confusion.....

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