Should I Terminate All Sessions?

A session is a logical entity in the database instance memory that represents the state of a current user login to a database. A single connection can have 0, 1, or more sessions established on it.

It’s important to note that sessions appear as system processes (SPID).There are various reasons why a Database Administrator (DBA) would have to kill sessions. Also, there is an option to kill all active sessions or identify the specific session causing problems and kill it without affecting the other active sessions.

This post covers the following: The reasons for killing sessions; recommended methods of killing sessions; the best time to kill sessions; benefits of killing sessions; any further problems that would arise from killing sessions.

Reasons for killing sessions

Occasionally, it may be necessary to kill an Oracle session that is associated with a running job. In this case, you’ve to first identify the session to be killed. Running jobs that were scheduled using the dbms_job package can be identified using the dba_jobs_running view. Use sql script with dba_jobs_running view along with the v$session and v$process views to gather all data needed about the running jobs.

DBAs must kill Oracle user processes, i.e. non-essential database processes, before shutting down database for backup or maintenance operations. The non-essential database processes comprise terminal sessions left connected after completion of real work.

If not killed, these active sessions finally cause problems when the database has to be shut down for either backup or maintenance operations. It’s usually necessary to kill orphaned sessions generated by users’ connections that consume server resources and license.

Because of Oracle’s internal locking, there are instances when it’s crucial to locate and kill Oracle sessions that are locking database resources.

Recommended method of killing sessions

1. Issuing the ALTER SYSTEM KILL SESSION command within the WinSQL is the only safe way to kill an Oracle session. If the marked session persists for some time it may be necessary to kill the process at the operating system level using the command:

#kill -9 PID

However, this is not recommended as it is dangerous and can lead to instance failures. The safe way of killing sessions is as follows:

SELECT.sid,
s.serial#,
       s.osuser,
       s.program
FROM   v$session s;

Then substitute the SID (Session Identifier) and SERIAL# (Serial Number) values of the relevant session into the statement below:

ALTER SYSTEM KILL SESSION ‘sid,serial#’

You could also add the IMMEDIATE keyword to force the kill as follows:

ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE

2. Before shutting down database, you must kill active sessions. As long as there is an active session, a normal-mode shutdown will hang. Oracle has an immediate shutdown mode, but this isn’t always reliable and, in some cases, results to an inconsistent backup. The abort shutdown option will shut down the database, but you then have to restart and perform a normal shutdown before any backup operations, or risk an inconsistent backup. Therefore, it is important to know how to kill these processes before completing such operations.

Note: Before killing sessions, it’s recommended to stop new sessions from connecting by using command below:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

3. To kill Oracle sessions that are locking database resources owing to Oracle internal locking, you have to list all the Oracle sessions, and then run a script to detect all locked sessions. This is because Oracle may not detect a dead session quickly enough to prevent a blockage in data access. Then you should create and run a script that automatically creates the “alter session” syntax to remove the locked sessions from oracle.

The best time to kill sessions

The best instances for killing sessions are as follows:

1.    Before shutting down database for backup or maintenance operations
2.    When there are orphaned sessions
3.    When there are Oracle sessions that are locking database resources

Benefits of killing sessions

1.    It prevents hanging of database or generation of inconsistent backups during database shutdown
2.    It stops orphaned sessions from consuming resources and licenses
3.    It removes sessions that lock database resources

Drawbacks of killing sessions

Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

Datavail can help you learn more about how to kill sessions and its benefits. Contact us today. With more than 400 database administrators worldwide, Datavail is the largest pure-play database services provider in North America. With 24×7 managed database services, including database design, architecture and staffing, Datavail can support your organization as it works with MySQL, regardless of the build you ultimately select.