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
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.
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.
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
what is the difference between
SELECT r.username, q.piece, q.sql_text
FROM V$SQLTEXT q, V$SESSION r
order by r.sid, piece
and the previous SQL?
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]
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.
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
where address = (select sql_address
where username = :uname
and sid = :snum)
ORDER BY piece
[Edited by jdorlon on 04-26-2001 at 01:07 PM]
Click Here to Expand Forum to Full Width