Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: v$session returns duplicate sids in subquery

    We are using a view built on v$session to retrieve a session's sid. The view looks like this:

    CREATE OR REPLACE VIEW VLOG_SESSION (SID)
    AS
    select sid from sys.v_$session where
    audsid=(select userenv ('sessionid') from dual)

    We have many customers using this view without any problems. We've got one though, that intermittently gets an ora-01247 single-row subquery returns more than one row error. It seems that v_$session is returning multiple rows with the same sid, but only when referenced in a subquery.

    Has anyone every encountered this before? It's on an 8.1.7.4 database.

    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Alternative solutions exist

    select sid from sys.v$_mystats where rownum = 1;

    or just add " and where rownum = 1" to your query.

  3. #3
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    SID is not a unique identifier of sessions. You need SID + SERIAL#. The reason is Oracle can assign SIDs faster than PMON can clean up dead sessions, so SIDs can get reused. As an example of how SID + SERIAL# are used together think of the command ALTER SYSTEM KILL SESSION 'SID,SERIAL#';.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  4. #4
    Join Date
    Dec 2003
    Posts
    2
    Thanks, I'd forgotten about serial#. Any idea how to keep Oracle from recycling SIDs so quickly??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •