-
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
-
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
-
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]
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|