Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    8

    Unanswered: View v$open_cursor complete sql_text

    Hi all

    My application is throwing "Too many open cursors" error due to statement caching. I am trying to figure out where in my application hogs up the open cursors. Using the following statement indicates 1 of the sql actually has 200 open cursors.

    select sql_text, count(*) as "OPEN", user_name
    from v$open_cursor
    group by sql_text, user_name order by count(*) desc;

    However it seems the field has size limit of varchar2(60) and most of the statements were truncated. Where can I find the rest of the statements?

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: View v$open_cursor complete sql_text

    use a join with v$sqltext (ADDRESS,HASH_VALUE) order by piece

    Originally posted by dito
    Hi all

    My application is throwing "Too many open cursors" error due to statement caching. I am trying to figure out where in my application hogs up the open cursors. Using the following statement indicates 1 of the sql actually has 200 open cursors.

    select sql_text, count(*) as "OPEN", user_name
    from v$open_cursor
    group by sql_text, user_name order by count(*) desc;

    However it seems the field has size limit of varchar2(60) and most of the statements were truncated. Where can I find the rest of the statements?

  3. #3
    Join Date
    Jan 2003
    Posts
    8
    Thx for the reply. Here's the query for reference.


    select voc.sql_text || vst.sql_text as sqltext
    from v$open_cursor voc inner join v$sqltext vst
    on voc.address=vst.address and voc.hash_value=vst.hash_value
    where voc.user_name='TEST'
    order by piece;


    Cheers
    -WK-

  4. #4
    Join Date
    Jan 2003
    Posts
    8
    Hmm.. above query doesn't seems correct. A chunk of statement is missing.

    -WK-

  5. #5
    Join Date
    May 2003
    Location
    France
    Posts
    112
    I think your order by is icomplete; try this :

    select vst.sql_text as sqltext
    from v$open_cursor voc inner join v$sqltext vst
    on voc.address=vst.address and voc.hash_value=vst.hash_value
    where voc.user_name='TEST'
    order by vst.hash_value,vst.address,piece;

  6. #6
    Join Date
    Jan 2003
    Posts
    8
    That works well.

    Thx
    -WK-

Posting Permissions

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