Sunday, June 29, 2014

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






7 comments:

  1. Really something grate in This Article thanks for sharing this. We are providing ORACLE courses training online. After reading this slightly am changed my way of introduction about my training to people. And also refer my website for ORACLE Training and solutions of ORACLE applications. Please Visit Us @ http://www.tectist.com/oracle-online-training.html/a/@

    ReplyDelete
  2. It was very nice article and it is very useful to Oracle Apps DBA Commerce learners.We also provide Cub training software online training.

    ReplyDelete
  3. Somebody necessarily help to make severely posts I might state. This is the first time I frequented your website page and to this point? I surprised with the research you made to create this particular post extraordinary. Well done admin..
    Oracle Training in Chennai

    ReplyDelete
  4. I want to kill those inactive session come from particular machine. Can you plse share the script

    ReplyDelete
  5. select 'ALTER SYSTEM KILL SESSION ' || ''''||sid||','||serial#||',@'||inst_id||'''' || ' immediate;' from gv$session where username='USER';

    Here is the script to kill all sessions for a user in RAC , Searched Everywhere in the internet bit couldn't get but finally was able to write one with a reference model . Please include this as well - Godwin Alex

    ReplyDelete