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:


Next, we will describe each of them.


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.


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.

kill session in oracle

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:


kill session in oracle 2

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:

kill session in oracle 3

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.


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.



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.

kill session in oracle 4

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

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


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

kill session in oracle 5

Figure 5: Toad for Oracle – Error popout

Try Toad free for 30 days.

Free 30-day Trial

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.


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.



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.

About the Author

Clarisa Maman Orfali

Clarisa is a System Engineer with more than 24 years of experience as a developer, teacher, and consultant in Information Technology. She was a Founder and CEO between 2013 to 2020 at ClarTech Solutions, Inc., a consulting firm specializing in Oracle APEX and Open Source technologies. Clarisa entered the world of Oracle technologies in 2009. After a few years, she discovered the power of application development with Oracle Application Express (APEX) and specialized in that area. Clarisa is from Argentina, and she is living and working in Irvine, California, in the United States. She's also a Co-Founder of the Argentina Oracle User Group (AROUG). In addition, she had actively participated in large and popular events such as the OTN Tour Argentina and Oracle APEX Tour Latin America after called Oracle Developer Tour. She's also authored the first three Spanish books about Oracle Application Express and recorded and published several Online Video Courses. She has a great passion for Oracle technologies and wants to expand that passion and influence, transferring her knowledge and experience to the entire Latin American and worldwide Oracle communities. You can always contact her through her Twitter account (@Clari707). Clarisa loves spending time with her family by enjoying outdoor activities with her two adored children, Melanie and Nicolas, as well as hanging out with her friends. Also, she loves traveling with her husband Julio worldwide and to takes new challenges at every moment of her life.

Start the discussion at forums.toadworld.com