DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: cursors

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Anyone know what does this query return?


    SELECT c.hash_value, count(1) nmero, PIECE, c.sql_text
    FROM v$open_cursor a, v$sqltext c
    WHERE a.hash_value = c.hash_value
    AND a.USER_NAME ='ICLICK'
    group by c.hash_value, piece, c.sql_text

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    Hi Pando,

    It looks to me line it returns the sql text of any open
    cursors by user 'ICLICK'. you might want to add an 'order by piece' to it. Each 'piece' is about 80 characters of sql text.

    Another thing I suggest, if you want to see the format of the sql as it was submitted to Oracle, is instead of V$SQLTEXT, use V$SQLTEXT_WITH_NEWLINES.

    I'm not sure what the point of the count(1) is since piece is going to make each line unique anyway.

    -John

  3. #3
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Solution

    Hi Pando, 26th April 2001 21:29 hrs chennai

    For all the parsed sql statement a hash value is created and it also has a unique address.

    you are trying to identify uniquely a cached cursor and its sql text for the sql statement you are executing.

    The two columns are dummy one list nothing but count of the present sql stat executed which is nothing but 1 and another each line of statement printed on sql

    If i am wrong please correct me.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    thanks

    what is the difference between

    SELECT r.username, q.piece, q.sql_text
    FROM V$SQLTEXT q, V$SESSION r
    WHERE q.hash_value=r.sql_hash_value
    and r.username='ICLICK'
    order by r.sid, piece
    /

    and the previous SQL?

    Cheers

    btw the count(1) is to find repeated SQL, anyway i didnt write the query some consultant did

    basically I want to know which SQL shows me the last SQL stmt issued by an user

    [Edited by pando on 04-26-2001 at 12:26 PM]

  5. #5
    Join Date
    Nov 2000
    Posts
    344
    Pando,

    I think the V$OPEN_CURSOR view shows only cursors that are still open. So if a user has no open cursors but is still connected to the DB, you will not see anything in V$OPEN_CURSOR for that user.

    V$SQLTEXT hangs onto stuff as long as it is still in the cache, so you are more likely to find the last executed
    query by your 2nd query that you posted.

    -John

    ps. this is the query I use in EZSQL to look for the last
    executed query (or currently executing query) by a
    session - it is basically the same

    select sql_text
    from V$sqltext_with_newlines
    where address = (select sql_address
    from V$session
    where username = :uname
    and sid = :snum)
    ORDER BY piece

    [Edited by jdorlon on 04-26-2001 at 01:07 PM]

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