Killing multiple sessions in Oracle Database

Hi,

We often get task to kill sessions which are active,inactive and the session related with a particular program.When we have one or two sessions we can kill it easily,if we have multiple sessions we can do it as follows:


Step 1:Check the Name of the Database
sqlplus "/as sysdba"

SQL>Select name from v$database;

Step 2: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

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

How to kill all active session in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' and type != 'BACKGROUND';

SQL>spool off

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to kill all ODI sessions in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>set lines 1000
SQL>set linesize 2000
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' AND USERNAME LIKE '%ODI%';

SQL>spool off


Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to get the list of Users and Processes running ODI sessions:

SQL> SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,s.sid,s.serial#,p.spid, s.username, s.program
 FROM   gv$session s
 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' AND S.USERNAME LIKE '%ODI%'; 

 How to kill a particular object blocking session:

 1.Find the tables(objects) which are locked:

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id and o.object_name='XX_OBJECT';

2.Killing the session holding the lock:

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

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

SERIAL#
----------
21091

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

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

Enjoy DBA tasks...

Happy DBA learning..


Best regards,

Rafi