Kill InActive Session - Database Learners

SELECT ‘ALTER SYSTEM KILL SESSION ‘||sid||’,’||serial#||’;’ FROM v$session where STATUS=’INACTIVE’;

select * from v$resource_limit where resource_name = ‘processes’;

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

SELECT ‘ALTER SYSTEM KILL SESSION ‘||””||sid ||’,’|| serial#||””||’ immediate;’ FROM v$session WHERE status =’INACTIVE’ AND USERNAME LIKE ‘%CDRPROJECT%’;

SELECT ‘ALTER SYSTEM KILL SESSION ‘||””||sid ||’,’|| serial#||””||’ immediate;’ FROM v$session WHERE status =’INACTIVE’ AND USERNAME LIKE ‘%VALIDATION%’;

SELECT ‘ALTER SYSTEM KILL SESSION ‘||””||sid ||’,’|| serial#||””||’ immediate;’ FROM v$session WHERE status =’INACTIVE’ AND USERNAME LIKE ‘%CDRDEV1PROJECT%’;

SELECT ‘ALTER SYSTEM KILL SESSION ‘||””||sid ||’,’|| serial#||””||’ immediate;’ FROM v$session WHERE status =’INACTIVE’ AND USERNAME LIKE ‘%CDR_REGRESSION%’;

select SID,SERIAL#,USERNAME,STATUS from v$session where STATUS=’INACTIVE’;

=========================================================================================================================

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.

======================================================================================================================

Killing session in Oracle RAC Database
Hi DBAs,
Whenever we find some locking issue in RAC Database and Developers requested to kill the session which is blocking and creating lock.,please make sure you are killing the correct session.Below steps can be very much useful.

Step 1:Find the Blocking sessionSQL> SET LINES 1000

SQL> select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session
where blocking_session is not null; 2

PROCESS SID BLOCKING_SESSION BLOCKING_INSTANCE

24822 1139 5366 1

1 rows selected.

Step 2:Check the Program which is blocking

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.sid=1139;

So ,1139 is the CALC program

Step 3:Find the Session details

SQL> select sid,serial#, INST_ID from gv$session where sid=’ 1139′;

   SID    SERIAL#    INST_ID

  1139      56959          3

Step 4:Kill the session immediately

SQL> alter system kill session ‘1139,56959,@3’ immediate;

System altered.

ALTER SYSTEM DISCONNECT SESSION ‘SID, SERIAL#’;