Why and How to kill a session in Oracle

If you are a Database Administrator (DBA), indeed, you have encountered the situation of needing to kill a session in the Oracle database.

Many different situations can lead you to need to kill a session in Oracle. The most important thing when killing a session in Oracle is that when the DBA works with these types of operations, they must be done with great care because if you kill the wrong session, you can corrupt the database. This would lead to a problem much bigger than merely the need to kill the session. It would be a catastrophe! That is why I always advise when you have to work with commands that, in my opinion, are “sensitive,” do it with great responsibility.

A general question that those just starting to work as Database Administrators often have is, ‘in what cases would a DBA generally need to kill an Oracle database session?’.

There may be many reasons for needing to kill a session in Oracle, and here I share some of the ones that I consider most common. For example, some developers or users may have a process “hanging” in the database, or taking much longer than usual, so they ask the DBA to kill the session in Oracle. So as a DBA, you must verify that it is a user process and not the background process of the database before performing any operation, and then determine the best way to do it.

For example, the DBA can kill the session immediately; no matter what the user or developer is doing, their session will be immediately killed. The other way to kill a session in Oracle would be to wait for the user or developer to finish performing their operations and then kill the session. Therein lies the difference between using the IMMEDIATE clause or not in the command. If used, it kills the session immediately, and if not used, the session will have a “marked for kill” state. Then it will be killed as soon as possible.

Other reasons may be that there is a high volume of resource consumption in the database, so killing the inactive user sessions will be necessary. However, it is essential to note that no background database processes should be killed because there is a high risk of corrupting the instance.

Oracle provides the end_session_clauses syntax for the ALTER SYSTEM statement, allowing you to kill the current session in various ways:

• KILL SESSION Clause
• DISCONNECT SESSION Clause

Next, we will describe each of them.

ALTER SYSTEM KILL SESSION

As we see in the Oracle documentation, “The KILL SESSION clause allows you to mark a session as terminated, roll back any transactions in progress, release all session locks, and partially recover session resources.”

To kill a session, you need to know:

• The session_id, specify the value of the SID column.
• The serial_number, specify the value of the SERIAL# column.
• The optional instance_id, specify the ID of the instance where the target session to be killed exists. You can find the instance ID by querying the GV$ tables.

ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In case of being in a RAC environment, you can specify the INST_ID.

ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’;

Suppose the session is currently performing some activity that needs to be completed, such as waiting for a response from a remote database or rolling back a transaction. In that case, Oracle Database waits for that activity to complete, marks the session as terminated, and gives us back control. If the wait takes a while, Oracle Database marks the end of the session and returns control to us with a message indicating that the session is marked to end. The PMON background process then marks the session as terminated when the activity is complete.

Whether or not the session has a transaction in progress, Oracle Database does not retrieve the entire state of the session until the session user issues a request to the session and receives a message that the session has ended.

Before killing a session, the DBA needs to know and perfectly identify the session that the user or developer asks him to kill. For this, it is a good practice to ask them if there is a way in which their session can be differentiated from other sessions. That way, you avoid killing the wrong session.

On the other hand, to identify the session, the DBA can use this SQL query:

SELECT sid, serial#, username FROM V$SESSION;

For more information, we can use this other SQL query to identify the program and schema name excluding the SYS schema:

SELECT sid, serial#, schemaname, program FROM V$SESSION WHERE schemaname != ‘SYS’;

In this example, we identify that we will kill the session of the APEXDEV user who has a session open in SQLPlus.

Figure 1: Toad for Oracle Editor Window – Identify session

Once we identify the session’s SID and serial number, we are ready to kill the session.

In this example, the SID corresponds to 287 and the serial number 62507.

We execute in our tool, in my case, the Toad for Oracle, the following command:

ALTER SYSTEM KILL SESSION ‘287,62507’ IMMEDIATE;

Figure 2: Toad for Oracle Editor Window – ALTER SYSTEM KILL SESSION

We execute the query to the V$SESSION table, and we can see that the session is no longer available:

Figure 3: Toad for Oracle Editor Window – Results

On the other hand, if the user/developer wants to run any commands on their current SQLPlus, they will need to log in again.

ALTER SYSTEM DISCONNECT SESSION

We use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Server). To use this clause, our instance must have the database open. In addition, we must also identify the session with the following two values from the V$SESSION view:

• The session_id, specify the value of the SID column.
• The serial_number, specify the value of the SERIAL# column.

ALTER SYSTEM DISCONNECT SESSION ‘sid, serial#’ POST_TRANSACTION;

ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE;

As we can see, this clause is more destructive than the other clause because it directly destroys the process on the server, which is equivalent to killing the server process from the operating system.

The POST_TRANSACTION setting allows ongoing transactions to be complete before the session is disconnected. If the session has no ongoing transactions, this clause has the same effect described as KILL SESSION.

The IMMEDIATE setting disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete. On the other hand, if we also specify the POST_TRANSACTION setting and the session has transactions in progress, the Oracle database will ignore the IMMEDIATE setting.

Instead, we don’t specify the POST_TRANSACTION setting, but because the session has no transactions in progress; this clause has the same effect as described for KILL SESSION IMMEDIATE.

For the following demo, I have another session open in Toad with APEXDEV, with SID: 32 and SERIAL#: 36107.

Figure 4: Toad for Oracle Editor Window – Identity new session

After we’ve identified the session, we execute the following statement:

ALTER SYSTEM DISCONNECT SESSION ‘32,36107’;

When we return to the APEXDEV session, we can see the message from Toad that the session has been killed.

Figure 5: Toad for Oracle – Error popout

Conclusion

As a database administrator, when faced with these types of requests from both developers and database users, make sure to kill the correct session because this type of database operation can lead to problematic situations if it is not treated with deep detail and attention.

References

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-SYSTEM.html#GUID-2C638517-D73A-41CA-9D8E-A62D1A0B7ADB

Related Links

Blog: Simplify SQL Trace with Quest® Toad® for Oracle DBA edition 

Blog: DB performance: 6 benefits from maintaining database health and stability 

Blog: Debugging PL/SQL Code with Toad for Oracle 

Have questions, comments?

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users.

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.