Why sid required in 'KILL SESSION' (Oracle SQL)?

Question

I have exam question and can't find answer. Why sid is mandatory required to perform KILL SESSION command?
KILL SESSION syntax: ALTER SYSTEM KILL SESSION 'sid,session#[@inst_id]' [IMMEDIATE];
Where sid is a 'unique' session identifier. Unique in quotes because it is unique in current moment, Oracle server can have, for example, session some sid, but after this session is over, this sid can be used for other session. Sid is analogy pid in OS.
Session# is a serial session number; it is a session counter. Server clear the counter at DB startup. So, session# is a really unique identifier for session.

I don't understand why sid is mandatory for KILL SESSION command. I think, it is possible to use session# only.

Thanks.

Answer 1

You need the SID and SERIAL#. The oracle reference has this to say:

SID Session identifier

SERIAL# Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID

So while a SID is unique, it can be re-used. So I guess the addition of a SERIAL# would ensure you don't kill a newly connected session that happens to use the same SID of what you were trying to kill (if they disconnected already before you executed the command).

UPDATE in response to Jury's comment:

I can't say why it is implemented like this, just my understanding of the implementation as per the documentation: SID is re-usable, the SERIAL# within a SID is not. So, as an example:

• [1,1] JOE connects and is assigned SID 1, SERIAL# 1
• JOE disconnects
• [1,2] TOM connects and is assigned SID 1 (reuse), but SERIAL# is incremented to 2
• [2,1] SUSAN connects and is assigned SID 2, SERIAL# 1

Why this implementation? Keeps numeric sizes manageable, perhaps? Minimize contention? Reuse of already allocated resources (represented by the SID)? I don't know.

Answer 2

I found it! Sequence for serial# is cyclic. Numbers will be reused after 2 billions logins if int used, or after 32k if short used. So, using serial# without anything else doesn't provide really unique enumerating.
But sid is unique at a moment. So, pair sid,serial# is really unique.
You can't use serial# or sid to manage sessions, you should use it in a pair.

Thank you for your time!

Answer 3

I know that this is an old topic, but I wanted to point out that serial# by itself isn't even close to a unique identifier, even at a particular point in time.

SQL> select serial#, count(*) from v$session group by serial#;

   SERIAL#   COUNT(*)
---------- ----------
         1         15
       983          1
         2          1
       505          1
         5          2
      7076          1
         7          2
        58          1
         3          3
         9          1
     22160          1

11 rows selected.

Out of the 29 sessions I have in my database currently, there are only 11 distinct serial#s.

EDIT: And this is a single instance database. In a RAC environment, you need to include the instance ID to get a truly unique session, from what I understand (I've never personally worked with a RAC database).