|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
|PDF · Mobi · ePub|
Sometimes it is necessary to terminate current user sessions. For example, you might want to perform an administrative operation and need to terminate all non-administrative sessions. This section describes the various aspects of terminating sessions, and contains the following topics:
When a session is terminated, any active transactions of the session are rolled back, and resources held by the session (such as locks and memory areas) are immediately released and available to other sessions.
You terminate a current session using the SQL statement
ALTER SYSTEM KILL SESSION. The following statement terminates the session whose system identifier is 7 and serial number is 15:
ALTER SYSTEM KILL SESSION '7,15';
To identify which session to terminate, specify the session index number and serial number. To identify the system identifier (SID) and serial number of a session, query the
V$SESSION dynamic performance view. For example, the following query identifies all sessions for the user
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = 'JWARD'; SID SERIAL# STATUS ----- --------- -------- 7 15 ACTIVE 12 63 INACTIVE
A session is
ACTIVE when it is making a SQL call to Oracle Database. A session is
INACTIVE if it is not making a SQL call to the database.
See Also:Oracle Database Reference for a description of the status values for a session
ORA-00028: your session has been killed
If, after receiving the
ORA-00028 message, a user submits additional statements before reconnecting to the database, Oracle Database returns the following message:
ORA-01012: not logged on
An active session cannot be interrupted when it is performing network I/O or rolling back a transaction. Such a 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
If the session is not making a SQL call to Oracle Database (is
INACTIVE) when it is terminated, the
ORA-00028 message is not returned immediately. The message is not returned until the user subsequently attempts to use the terminated session.
When an inactive session has been terminated, the
STATUS of the session in the
V$SESSION view 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
SELECT SID,SERIAL#,STATUS,SERVER FROM V$SESSION WHERE USERNAME = 'JWARD'; SID SERIAL# STATUS SERVER ----- -------- --------- --------- 7 15 INACTIVE DEDICATED 12 63 INACTIVE DEDICATED 2 rows selected. ALTER SYSTEM KILL SESSION '7,15'; Statement processed. SELECT SID, SERIAL#, STATUS, SERVER FROM V$SESSION WHERE USERNAME = 'JWARD'; SID SERIAL# STATUS SERVER ----- -------- --------- --------- 7 15 KILLED PSEUDO 12 63 INACTIVE DEDICATED 2 rows selected.