The Oracle RDBMS provides the DBA with the ability to kill a user’s session. On occasion you will find a particular account which is causing performance issues or performing an action which may be harmful to the database. In this situation, it may be best to kill the session. This article covers the use of “alter system” command to kill a users session.
1. Logon to you Oracle database server.
2. Logon to SQLPLUS with sysdba privileges.
mylinux:> sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 02 08:15:39 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
3. In this example, we know the session causing the issue is owned by the user LJCATT. To kill the session we must identify the SID and SERIAL# of the session owned by LJCATT. Execute the following SQL to get the SID and SERIAL# from his session.
SQL> select SID, SERIAL#, username from v$session where username=’LJCATT’;
SID SERIAL# USERNAME
———- ———- ——————————
3979 108 LJCATT
SQL>
4. To kill the session use the following command: alter system kill session ‘SID,SERIAL#’ immediate;
SQL> alter system kill session ‘3979, 108′ immediate;
System altered.
SQL>
5. Re-execute the SQL in step 3 to verify that the session has been killed.
SQL> select SID, SERIAL#, username from v$session where username=’LJCATT’;
no rows selected
SQL>
NOTE: If the user has performed a large amount of updates, insert, or deletes; the kill operation may take awhile to complete, due to rollback operations.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com