How To kill Blocked/Locked Oracle Database Session

Many times during our development process in development instance we come across locks in the database. These locks are caused by some sessions. We can easily kill these sessions to remove the locks without being dependent on DBAs.

Identify the Locking Sessions:

Run the following query to find out which sessions are creating locks in the server:

select sid, serial#, username, status, server

  from v$session;

Output Sample:

       SID    SERIAL#    USERNAME     STATUS   SERVER

---------- ---------- ------------------------------ -------- ---------

        23         38             ABCD                ACTIVE   DEDICATED

        25          1               APPS                ACTIVE   DEDICATED

        26      10516          SYSTEM         ACTIVE   DEDICATED

        29         93             APPS                ACTIVE   DEDICATED

These session can be killed by the following query by passing the SID, SERIAL# combination.

Example:

alter system kill session '29, 93';