How to find SID
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: How to find SID

  1. #1
    Join Date
    Jul 2003
    Posts
    134

    How to find SID

    I know V$SESSION holds the SID info; but I have several sessions open for the User SCOTT; and I'm currently working on a particular session with some transaction. How can I find out what is the SID of this current session I am working on, assuming there is more than one SCOTT session open.

    Can anyone pls tell me.
    Last edited by Vipassana; 07-09-2004 at 03:11 AM.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    For your current session:

    select * from v$session
    where audsid=SYS_CONTEXT ('USERENV', 'SESSIONID')

  3. #3
    Join Date
    Jul 2003
    Posts
    134
    Originally posted by DaPi
    For your current session:

    select * from v$session
    where audsid=SYS_CONTEXT ('USERENV', 'SESSIONID')
    Thanks bud.. that's exactly what I needed.

    BTW, what does "audsid" mean? I mean how is it different from sid?
    Last edited by Vipassana; 07-09-2004 at 03:52 AM.

  4. #4
    Join Date
    Jul 2003
    Posts
    134
    Originally posted by DaPi
    For your current session:

    select * from v$session
    where audsid=SYS_CONTEXT ('USERENV', 'SESSIONID')
    Sorry, just realized this didn't help me. I got two rows for this query:


    SQL> select sid from v$session
    2 where audsid=SYS_CONTEXT ('USERENV', 'SESSIONID');

    SID
    ----------
    214
    243

    How to know which is the one I am working on?

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    AUDSID = Auditing session ID
    It is a Oracle-type sequence allocated to sessions: so always increasing, never reused. (SID numbers are reused).

    I've seen this behaviour (in 8.1.7) with Parallel Query - it will "spawn" additional sessions(?) - different SID, same AUDSID - to run several parallel processes. Under some circumstances (which? - I have no idea) these sessions remain in v$session after the query is finished.

    - 0 - 0 - 0 -

    I've just realised that the above two paragraphs are contradictory in regard to reuse of AUDSID's. Perhaps some kind of distinction needs to be made between sessions and sub-sessions? I'm getting out of my depth here.
    Last edited by DaPi; 07-09-2004 at 04:32 AM.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    SELECT sid FROM v$mystat WHERE ROWNUM < 2;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jul 2003
    Posts
    134
    Originally posted by jmodic
    SELECT sid FROM v$mystat WHERE ROWNUM < 2;
    Yes, this will fetch one row; but what does that mean anyway? I mean how can this justify that that's the SID of the current session?

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by Vipassana
    . . . how can this justify that that's the SID of the current session?
    That's how it's defined: http://download-west.oracle.com/docs...h394.htm#30323

  9. #9
    Join Date
    Jul 2003
    Posts
    134
    Originally posted by DaPi
    That's how it's defined: http://download-west.oracle.com/docs...h394.htm#30323
    I thk you missed the Q. The question was - "How can we say that the SID of the least ROWNUM is the correct one, when there are more than one SIDs for the same session" !!!

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If you look in v$mystat, do you see more than one SID?

Posting Permissions

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



Click Here to Expand Forum to Full Width