3.5 How do I…Kill a user’s session?Problem
When developing applications, I have occurrences of runaway and unwanted processes. When a query runs for an unacceptable amount of time, I want to terminate the process. When I get a call from a user or developer wanting a session killed, I don’t want to leave SQL*Plus. How can I kill a user’s session using SQL*Plus?
Technique
To kill a user’s session using SQL*Plus, you must be connected to the database as a user with the ALTER SYSTEM privilege. The SYS and SYSTEM users have this privilege by default. The V$SESSION view contains information about sessions connected to the database. Figure 3.7 and Figure 3.8 show the description of the V$SESSION view in SQL*Plus.
The V$SESSION view can be queried to find out what sessions a user has running on the database. The ALTER SYSTEM KILL USER statement is used to terminate a user’s session through SQL*Plus. It requires the session identifier (SID) and the process serial number as part of the command.
Steps
1. Run SQL*Plus and connect as the SYS or SYSTEM user account. Load CHP3_6.sql into the SQL buffer with the GET command. The file contains a SQL statement that can be used to determine session information for a user. The USERNAME column identifies which user owns the session. Determine the SID and serial number for the session to be killed.
SQL> GET CHP3_6.sql
1 SELECT SID, SERIAL#, STATUS
2 FROM V$SESSION
3* WHERE USERNAME = ‘WAITE’
Line 1 returns the SID, serial number, and status of the session. Line 2 specifies the V$SESSION data dictionary view as the source of the query. Line 3 causes records to be returned only for the WAITE user account.
2. Run the query to return the active connections.
SQL> /
SID
SERIAL#
STATUS
-----
-------
------
9
3
ACTIVE
11
26
INACTIVE
The output from the query gives the information needed in the next step to kill the process. Notice that the STATUS column was queried. This column is important because a user can have more than one session, and only the right one should be terminated. A common example of multiple sessions is when Oracle Reports creates a separate session any time a report is run.
3. Load CHP3_7.sql into the SQL buffer. The ALTER SYSTEM statement contained in the file contains the KILL SESSION keywords to kill an Oracle session.
SQL> GET CHP3_7.sql
1 ALTER SYSTEM
2* KILL SESSION ‘&sid,&serial’
Line 1 contains the ALTER SYSTEM keywords required to kill an Oracle session through SQL*Plus. Line 2 contains the KILL SESSION option and specifies the SID and serial number with the &SID and &SERIAL substitution variables.
4. Run the statement supplying the SID and serial number of the session to be killed for the &SID and &SERIAL substitution variables.
SQL> /
Enter value for sid: 11
Enter value for serial: 26
old 2: kill session ‘&sid, &serial’
new 2: kill session ‘11,26’
System Altered
After the statement has been executed, the session is terminated.
How It Works
The V$SESSION view is a virtual performance view. It is constantly changing as the dynamics of the system change. There are many of these views that can be queried at any time. Many performance monitoring tools regularly query these views to provide online monitoring of the system. The ALTER SYSTEM statement enables a user with the ALTER SYSTEM privilege to make changes to the system immediately. As soon as this statement is executed, the user’s session will be killed. Steps 1 and 2 query the V$SESSION view to get information about the session to be killed. The SID and SERIAL# columns are required to kill a session with the ALTER SYSTEM statement. Steps 3 and 4 execute an ALTER SYSTEM statement to kill the session.
Comments
Unwanted or runaway sessions can steal CPU and other system resources from your system. Terminating processes through SQL*Plus is one of the ways to keep them from robbing your system of performance. It is important to develop a strategy for monitoring process utilization and performance. The V$ views enable you to create your own programs for monitoring database resources.