oracle kill sessions script with code examples

Introduction
So you are an Oracle database administrator who is looking for a foolproof way to kill sessions on an Oracle database. Look no further as we have just the right solution for you. In this article, we will show you how to write a script that can be used to kill sessions in Oracle database. We will include practical code examples that you can copy and paste into your environment, then customize your personal needs.

Background
Every Oracle database administrator is fully aware of the importance of monitoring sessions. There are situations where you may notice that some sessions are active and not responding, causing performance issues on the server. The effects of this could be reduced server capacity, inability to connect to the server, or the database not running as expected. Periodically, it may become necessary to kill sessions to enhance the database's overall performance.

Killing sessions has a significant impact on the database server's performance, which means that it is not something you can randomly undertake. Often, the process is initiated to stop a session from accessing the database, especially if the user does not have permission or if the session is expired. Sometimes, external factors like a system halting or the database becoming unresponsive may necessitate killing one or more sessions.

The Oracle Kill Sessions command
The Oracle Kill Session command is used to kill specific sessions that may have become unresponsive or have violated specific rules. The syntax of the command is as follows:

ALTER SYSTEM KILL SESSION ‘sid,serial#’

Syntax breakdown:

Sid: This is the session identifier, a unique identifier assigned to each user/client that logs into the database.
Serial #: This is the series number assigned to each session created on the server.

Connecting to Oracle Database
Before you start killing sessions, you first need to connect to the database. We will show you how to connect to the database using SYS as an example below:

CONN SYS/password AS SYSDBA

If you know the specific user who is connected, you can connect to the database using the following:

CONN username/password

Killing specific sessions
You can kill a specific session using the Oracle Kill Session command. Here is the syntax of the command:

ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In the SQL command above, you need to replace ‘sid, serial number’ with the unique SID and serial number of the user/client session you want to kill.

Here is an example:

ALTER SYSTEM KILL SESSION ‘400,1234’;

The script above will kill the session with the SID equal to 400 and serial number 1234. To check the session's SID and serial number, you can use the following statement:

SELECT sid, serial# FROM v$session WHERE username=’username’;

You can replace the ‘username’ with the name of the user whose session you want to kill.

Killing Inactive Sessions
Inactive sessions are sessions that have been idle for a long time, and killing them can free up resources on the server. You can use the following script to kill inactive sessions:

SELECT 'alter system kill session ' || '''' || sid || ',' || serial# ||'''' || ' immediate;' 
FROM v$session 
WHERE username is not null 
AND status='INACTIVE' 
AND last_call_et > 3600;

In the above script, we use the v$session view to check for inactive sessions that have been idle for more than one hour (3600 seconds). We then generate kill session commands for each inactive session found.

You can run the above script to kill all the inactive sessions in your Oracle database.

Killing Multiple Sessions at Once
You can kill multiple sessions at once by generating a script that contains multiple Oracle Kill Session commands. The script will execute all the commands at once, killing all the specific sessions you have identified.

Here is an example script:

SELECT 'alter system kill session ' || '''' || sid || ',' || serial# ||'''' || ' immediate;'
FROM v$session 
WHERE sid 
IN (SELECT SID FROM v$session WHERE username='USERNAME');

The above script will generate kill session commands for all active sessions connected via the user 'USERNAME.'

Testing Oracle Kill Sessions Script
Before you can start killing sessions on your Oracle database, you must first test the script you have generated to ensure that it is working as expected.

Here are a few tips to help you test your script:

Run the script in a test or development environment before running it in production.
Test the script using a single session before trying to kill multiple sessions.
Run the script during a maintenance window to minimize the impact on active sessions.

Conclusion
Killing sessions is an essential task that every Oracle database administrator should know how to do. Oracle provides the Kill Session command to help you accomplish this task. The scripts we have provided in this article should get you started on writing your personalized Oracle Kill Sessions script better. However, always remember to test it in a test environment before running it in production. Users log in and interact with Oracle databases typically initiate active sessions. Therefore, as the database administrator, you must identify and terminate inactive sessions to maximize the database's performance.

I will provide more information on the previous topics.

Background on Monitoring Sessions
Monitoring sessions is a crucial task for any Oracle database administrator. Sessions refer to the interaction between a user or client and the database. A session typically starts when a user connects to the database and ends when the user disconnects. During a session, the user can execute SQL statements that query, insert, update or delete data from the database. Each session consumes resources on the server, including CPU, memory, and I/O.

A database administrator can use several tools and techniques to monitor sessions. For instance, one can use the v$session view, which provides information about all active sessions currently connected to the database. The view contains columns that display the session ID, session status, username, application, and client information. Other tools that can be used for monitoring sessions include Oracle Enterprise Manager (OEM), Statspack, AWR reports, and SQL Trace.

Killing Specific Sessions
Killing a specific session is an effective way to eliminate performance issues that may be caused by that session. To kill a specific session, you need to know its session ID (SID) and serial number. You can obtain this information by querying the v$session view.

To kill a session, you can use the ALTER SYSTEM KILL SESSION command. The command syntax is as follows:

ALTER SYSTEM KILL SESSION 'sid,serial#';

Where 'sid' is the session ID, and 'serial#' is the session serial number. The command immediately terminates the session and releases all resources associated with it. The session being terminated cannot be resumed.

Killing Inactive Sessions
Inactive sessions are sessions that have been idle for a long time, and they are consuming resources without performing any useful work. Killing inactive sessions is a way to free up resources on the server.

To kill inactive sessions, you can use the v$session view to identify sessions that have been idle for a certain period. For example, you can identify sessions that have been idle for more than one hour using the following query:

SELECT sid, username, last_call_et/60 AS "Idle Time (Mins)" 
FROM v$session 
WHERE status='INACTIVE' 
AND last_call_et > 3600;

You can then use the ALTER SYSTEM KILL SESSION command to terminate the identified sessions.

Killing Multiple Sessions at Once
In some cases, you may need to kill multiple sessions simultaneously. For example, you may need to perform maintenance on the server or resolve a performance issue that is affecting multiple users.

To kill multiple sessions, you can use a script that generates multiple ALTER SYSTEM KILL SESSION commands. The script can be customized to target specific sessions based on criteria such as username or status.

Here's an example of a script that kills all inactive sessions connected via the user 'USERNAME':

DECLARE
  sessions_to_kill VARCHAR2(32767);
BEGIN
  FOR sessions IN (SELECT sid,serial# FROM v$session WHERE username='USERNAME' AND status='INACTIVE' AND last_call_et > 3600)
  LOOP
    sessions_to_kill := sessions_to_kill || 'ALTER SYSTEM KILL SESSION ''' 
                     || sessions.sid || ',' || sessions.serial# ||''';'|| chr(10);
  END LOOP;
  dbms_output.put_line(sessions_to_kill);
  EXECUTE IMMEDIATE sessions_to_kill;
END;
/

The script generates multiple ALTER SYSTEM KILL SESSION commands that target all inactive sessions connected via the user 'USERNAME.' The script output is sent to the console, showing the commands that were run.

Testing Oracle Kill Sessions Script
Before you start killing sessions on your Oracle database, you must first test the script you have generated to ensure that it is working as expected.

Here are some tips for testing your Oracle Kill Sessions script:

Run the script in a test or development environment before running it in production.
Test the script using a single session before trying to kill multiple sessions.
Run the script during a maintenance window to minimize the impact on active sessions.

oracle kill sessions script with code examples

Popular questions

Tag

Ajay Kemparaj

Leave a Reply Cancel reply

Popular questions

Tag

Ajay Kemparaj

Leave a Reply Cancel reply

Related Posts