This content has been marked as final. Show 11 replies
this will return the currently executing sql
select s.sid,sq.sql_text from v$session s, v$sqlarea sq
where s.SQL_ADDRESS = sq.ADDRESS and s.sql_hash_value=sq.hash_value
this will get you all open cursors:
from v$session s, v$sqlarea sq, v$open_cursor oc
and s.saddr = oc.saddr
and sq.address=oc.address and sq.hash_value=oc.hash_value
so this might be the query :
from v$sqlarea s1,v$session s2
s2.SQL_ADDRESS = s1.ADDRESS
I tried this
I have just seen two oracle top sessions in unix and try to view their sqls with above query , but unfortunately no rows selected.
I guess they were thrown out of the cache very quickly
Andrei Kübar wrote:It's better to join on the hash_value rather than the address (and include the address if you are are worried about two statements having the same hash_value) as the hash_value is a "pseudo-"indexed access path into the underlying x$ structure.
if the v$session.status='INACTIVE' then SQL_ADDRESS will be empty, meaning the execution has ended
in this case you can see which sql was the last executed
just join on v$session.prev_sql_addr instead of sql_address
It's also better in 9i to join to v$sql, rather than v$sqlarea - although this will give you multiple copies when a statement has multiple child cursors - as v$sqlarea is an aggregate of the underlying x$ and may make it impossible for oracle to find an efficient method for getting the data.
(Things change in 10g - where the sql_id appears, the child_number gets including in extra views, and v$sqlstat comes into existence to substitute for v$sqlarea, and v$sqlarea is no longer an aggregate view anyway and operates on mutexes rather than latches).
"Be very, very careful what you put into that head because you will never, ever get it out."
I have a question about this.
A user session have a lot of cursors opened... Like this:
USER_NAME SID # CURSORS PERCENTAGE
------------------------------ ---------- ---------- ----------
USER 126 1091 83.92
Then with your query, I have the output of all the querys executed by this user..There are a lot of INSERT.
So my question is:
If the INSERT statment do not have a commit every n records...but just one at the end of the script, this can be the cause of all this opened cursors?
If we add in the script a commit every 1000 records for exemple, each commit will close the cursor opened by the insert statment?
Thanks for all the info.