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)
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 22.214.171.124 database.
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#';.