This section describes the various aspects of terminating sessions, and includes the following topics:
When a session is terminated, the session's transaction is rolled back and resources (such as locks and memory areas) held by the session are immediately released and available to other sessions.
Terminate a current session using either the Disconnect Session menu item of Server Manager, or the SQL command ALTER SYSTEM...KILL SESSION.
The following statement terminates the session whose SID is 7 and serial number is 15:
ALTER SYSTEM KILL SESSION '7,15';
The following query identifies all sessions for the user JWARD:
SELECT sid, serial# FROM v$session WHERE username = 'JWARD';
SID SERIAL# STATUS --------- ---------- -------- 7 15 ACTIVE 12 63 INACTIVE
A session is ACTIVE when it is making an SQL call to Oracle. A session is INACTIVE if it is not making an SQL call to Oracle.
See Also: For a complete description of the status values for a session, see Oracle7 Server Tuning.
ORA-00028: your session has been killed
If, after receiving the ORA-00028 message, a user submits additional statements before reconnecting to the database, Oracle7 returns the following message:
ORA-01012: not logged on
If an active session cannot be interrupted (for example, it is performing network I/O or rolling back a transaction), the session cannot be terminated until the operation completes. In this case, the session holds all resources until it is terminated. Additionally, the session that issues the ALTER SYSTEM statement to terminate a session waits up to 60 seconds for the session to be terminated; if the operation that cannot be interrupted continues past one minute, the issuer of the ALTER SYSTEM statement receives a message indicating that the session has been "marked" to be terminated. A session marked to be terminated is indicated in V$SESSION with a status of "KILLED" and a server that is something other than "PSEUDO."
When an inactive session has been terminated, STATUS in the view V$SESSION is "KILLED." The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.
In the following example, the DBA terminates an inactive session:
SVRMGR> SELECT sid, serial#, status, server
2> FROM v$session
3> WHERE username = 'JWARD';
SID SERIAL# STATUS SERVER
---------- ------- -------- ---------
7 15 INACTIVE DEDICATED 12 63 INACTIVE DEDICATED 2 rows selected.
SVRMGR> ALTER SYSTEM KILL SESSION '7,15'; Statement processed.
SVRMGR> SELECT sid, serial#, status, server
2> FROM v$session
3> WHERE username = 'JWARD';
SID SERIAL# STATUS SERVER
---------- ------- -------- ---------
7 15 KILLED PSEUDO 12 63 INACTIVE DEDICATED 2 rows selected.