This content has been marked as final. Show 3 replies
I'm working on the below query which should give me the details of all active sessions and the queries they are running. The query also gives sid, serial#, spid, and inst_id in case I want to kill the session.
I'm getting duplicates because of the child sessions. Is there a way to differentiate the parent and child session?
SELECT sess.inst_id, sess.sid, sess.serial#, sess.username, sess.schemaname, sess.osuser ||'@' ||sess.machine, sess.program, sess.logon_time, proc.spid, ar.sql_fulltext, sess.status FROM gv$session sess, gv$process PROC, gv$sqlarea ar WHERE sess.paddr = proc.addr AND sess.inst_id = proc.inst_id AND sess.sql_address = ar.address AND sess.sql_hash_value = ar.hash_value AND sess.inst_id = ar.inst_id AND sess.status = 'ACTIVE' AND (proc.background <> 1 OR proc.background IS NULL)
There must be, because in TOAD session browser the child sessions are "attached" to the parent session.
I am sorry I do not know how the TOAD programmers query the gv$ views ;-) I just use the session browser myself.
But most likely there are some other forum users who can tell how to differentiate. Wel'll just wait and see. Sorry I can't be of more help ;-)