This discussion is archived
3 Replies Latest reply: Nov 20, 2012 7:27 AM by Kim Berg Hansen RSS

Querying gv$session

774875 Newbie
Currently Being Moderated
Hi,

I'm running the below query connected to Instance 1 of an Oracle DB with 2 nodes through sqldeveloper.
select * from gv$session where machine = 'CHJAYARA-LAP';
I've just one instance of sqldeveloper opened and just one active connection through it. However the above query returns 3 rows, 2 rows with inst_id = 1 and 1 with inst_id = 2. One row has program = SQL Developer, the others have oracle@XXXXXXX.com and oracle@YYYYYY.com.

Could you please explain the additional 2 rows in gv$session when all I've is just one connection to the DB.


Thanks,
CJM
  • 1. Re: Querying gv$session
    Kim Berg Hansen Expert
    Currently Being Moderated
    Probably child sessions for parallel query or queries to each node.
    If all three have the same AUDSID value, it is really the same connection - then the two with program "oracle@<nodename> (PZ99)" are the child sessions.
  • 2. Re: Querying gv$session
    774875 Newbie
    Currently Being Moderated
    Thanks Kim.

    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)
    Thanks,
    CJM
  • 3. Re: Querying gv$session
    Kim Berg Hansen Expert
    Currently Being Moderated
    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 ;-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points