5.8 Terminating Sessions

«« Previous
Next »»

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.

5.8.1 About Terminating Sessions


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';

You can also use the DBMS_SERVICE.DISCONNECT_SESSION procedure to terminate sessions with a named service at the current instance.

5.8.2 Identifying Which Session to Terminate


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 jward:

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.

5.8.3 Terminating an Active Session


Terminating an active session ends the session.

If a user session is processing a transaction (ACTIVE status) when you terminate the session, then the transaction is rolled back and the user immediately receives the following message:

ORA-00028: your session has been killed

If, after receiving the ORA-00028 message, a user submits additional statements before reconnecting to the database, then 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 PSEUDO.

If you are using Application Continuity, then an active session's activity is recovered when the session terminates. If you do not want to recover a session after you terminate it, then you can include the NOREPLAY keyword in the ALTER SYSTEM statement. For example, the following statement specifies that the session will not be recovered:

ALTER SYSTEM KILL SESSION '7,15' NOREPLAY;

If you use the DBMS_SERVICE.DISCONNECT_SESSION procedure to terminate one or more sessions, then you can specify DBMS_SERVICE.NOREPLAY for the disconnect_option parameter to indicate that the sessions should not be recovered by Application Continuity. For example, to disconnect all sessions with the service sales.example.com and specify that the sessions should not be recovered, run the following procedure:

BEGIN
  DBMS_SERVICE.DISCONNECT_SESSION(
    service_name      => 'sales.example.com',
    disconnect_option => DBMS_SERVICE.NOREPLAY);
END;
/

5.8.4 Terminating an Inactive Session


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 ORA-00028 message.

In the following example, an inactive session is terminated. First, V$SESSION is queried to identify the SID and SERIAL# of the session, and then the session is terminated.

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.

«« Previous
Next »»