cursors

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

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

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

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 :o

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

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]