How to Kill Database Session

In my Today's blog I want to discuss how to kill the database session.

Step1. For example I have connected to Oracle Database viz a user named HR with the help of SQL Developer. You can login viz sqlplus by giving the proper credentials of the User Id you are going to use.

Here I have login to Oracle Database viz User HR as shown in the below snap.





When you got login into the database means the user session has started and you can access the database by executing the query against the database.

Ex: select * from Ir;  -----> giving you just an example.









Step2: Now connect to database as SYSDBA  to kill the above session (user= HR)

You can connect to the database viz command prompt or direct from the sqlplus(It's your choice and depends upon your comfortable with the tools)

Here I have used Command Prompt and login as sysdba to database




Step3: Note:  To kill a database session you have to required two things: sid,serail#

To find out the sid and session the below query helps out.

select sid, serail# from v$session where username='name of the user' ;




Note: Remember one thing though sqlplus is case insensitive but when will you give the user name you  have to be handled it with carefully. You have give the proper username.You can see I have given ' hr '  in 1st case and it gave me error : no rows selected.
So you have to be more carefully on this.

Note: So from the above query we have received the SID and Serial#.

Step3. The final step and here we go.

To kill the session in windows platform the sql query you have use :

Alter system kill session 'sid, serial#';




Step4: Successfully you have killed the session. For prove go to the user- HR opened in the sql developer and try to execute any sql query on it ,it will show you to reconnect to the database.




Done !!!

In Unix Approach

To kill the session on UNIX or Linux operating systems, first identify the

session, then substitute the relevant SPID into the following command.

    % kill spid

If after a few minutes the process hasn't stopped, terminate the session using

the following.

    % kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using.

    % ps -ef | grep ora

The session thread should be killed immediately and all resources released.

 Scenario2:  How to find blocking session and kill the session from database

Why Blocking issue: 

During some up gradation to the database or when you are any patching to the database viz adpatch, deadlock will happen when some jobs depends on resource where another job its holding the resource.Using adctrl ,you can find two jobs in running state there will be no log updates. 

* To find blocking session jobs below query will useful. It will return two rows.

select process,sid, blocking_session from v$session where blocking_session is not null;

E.g.  SQL> select process,sid, blocking_session from v$session where blocking_session  is not null;


PROCESS     SID        BLOCKING_SESSION
————      ———-  —————-
12                 32                  36
144               36                   38


Step2:  Find the serial number for the Blocking Session to kill

select SERIAL# from v$session where SID=<SID number>

E.g.
SQL> select SERIAL# from v$session where SID=32;
SERIAL#
———-
456

Step3: To kill session by using SID and Serail#

alter system kill session ‘SID,SERIAL#’;

E.g.  SQL> alter system kill session ’32,456';
System altered.

Have a great day!!