Killing Sessions

How to kill all inactive session in Database?

sqlplus "/as sysdba"

SQL>set heading off

SQL>spool kill12.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'

 FROM v$session

 WHERE status ='INACTIVE' and type != 'BACKGROUND';

SQL>spool off

then execute the sql script

SQL> @kill12.sql

Killing the session holding the lock:

--Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID='12';

SERIAL#

----------

21091

SQL> alter system kill session '667,21091';

SELECT s.blocking_session, s.sid, s.serial#,  s.seconds_in_wait FROM v$session s WHERE blocking_session IS NOT NULL;

ALTER SYSTEM DISCONNECT SESSION:

The ALTER SYSTEM DISCONNECT SESSION 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 basic syntax is similar to the KILL SESSION command with the addition of the

POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be

substituted into one of the following statements.

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

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

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 rolled back immediately.

The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation

states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram

suggests both clauses are optional, but in reality, one or both must be specified or you

receive an error.

 Identifying blocked objects:

The view v$lock we've already used in the queries above exposes even more information.

There are different kind of locks - check this site for a complete list:

http://download.oracle.com/docs/cd/B13789_01/server.101/b10755/dynviews_1123.htm#sthref3198

If you encounter a TM lock is means that two sessions are trying to modify some data but

blocking each other. Unless one sessions finished (commit or rollback), you'll never have

to wait forever.

The following queries shows you all the TM locks:

SELECT sid, id1 FROM v$lock WHERE TYPE='TM'

SID        ID1

92          20127

51          20127

 

The ID you get from this query refers to the actual database object which can help you to

identify the problem, look at the next query:

SELECT object_name FROM dba_objects WHERE object_id=20127