Unlock Database Objects by Using Kill Session Command

Unlock Database Objects by Kill Session Command

Sometime you are trying to compile package but its not compiling or showing continuous waiting status

or you will get error like-

ORA-04021: timeout occurred while waiting to lock object

Same thing happens in case of table when you are trying to insert/update data.

So in these cases, maximum chances are that object got locked.

You can verify that object has locked or not by running below query-

SELECT

  oracle_username,

  os_user_name,

  locked_mode,

  object_name,

  object_type

FROM

  v$locked_object a,

  dba_objects b

WHERE

  a.object_id=b.object_id;

If your object got populated in above query output, then means its locked.

You can unlock object by killing that session using ALTER SYSTEM KILL SESSION syntax for killing session.

First you need to dervie SID and SERIAL of that session. You can derive it by running below query-

SELECT

  s.osuser "O/SUser",

  s.username "OraUser",

  s.sid "SID",

  s.serial# "Serial",

  s.process "PID",

  s.status "Status",

  l.name "ObjLocked",

  l.mode_held "Lock Held"

FROM

  V$SESSION s,

  DBA_DML_LOCKS l,

  V$PROCESS p

WHERE

  l.session_id = s.sid AND

  p.addr = s.paddr;

Below are various syntax for killing session-

ALTER SYSTEM KILL SESSION 'sid,serial#';

The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself.In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible. 


ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

The syntax is an alternative method for killing Oracle sessions.
Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system.

The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.