Tuesday, December 12, 2017

 How to kill Inactive sessions in Oracle Database?

Sometimes there are so many inactive sessions available in the database. Due to these inactive sessions, and database responds slow performance so we need to kill them using the below steps.

Step 1. Find total session details.

SQL> select status,count(*) from v$session group by status;
Step 2. Find sid & serial# for inactive sessions.
SQL> SELECT sid, serial#, status, username FROM v$session where status='INACTIVE';

Step 3. Kill session using below command.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 

If you have multiple sessions for killing, you must prepare the script.

Script for all sessions:

SQL> SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session;

The script only for Inactive sessions:

SQL> SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session 
     where status='INACTIVE';                                                  

The above queries will write the queries for you to kill Inactive sessions

to makes your task very easy.



SET LINESIZE 250
COL USERNAME FOR A12
COL OBJECT_NAME FOR A10
COL SID FOR A20
COL LOCK_MODE FOR A10
COL CTIME FOR A30
COL MACHINE FOR A25

SELECT S.USERNAME,
SUBSTR(O.NAME, 1, 15) OBJECT_NAME,
DECODE(LV, 1, 'Holder: ' || S.SID, 'Waiter: ' || S.SID) SID,
DECODE(L.LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive',

TO_CHAR(L.LMODE)) LOCK_MODE,
TRUNC(L.CTIME / 3600) || ':' || TRUNC(MOD(L.CTIME, 3600) / 60) || ':' || MOD(L.CTIME, 60) CTIME,
S.STATUS,
S.MACHINE,
S.SQL_ID,
Q.SQL_TEXT
FROM (SELECT /*+ NO_MERGE */(3-LEVEL) LV,
INST_ID,
SID,
TYPE,
LMODE,
CTIME
FROM (SELECT /*+ NO_MERGE */A.INST_ID,
A.SID,
A.TYPE,
A.LMODE,
A.REQUEST,
CASE
WHEN REQUEST = 0 THEN ID1
END ID1,
CASE
WHEN REQUEST > 0 THEN ID1
END ID3,
A.CTIME
FROM GV$LOCK A
WHERE A.TYPE <> 'MR') START WITH REQUEST > 0 CONNECT BY PRIOR ID3 = ID1) L,
GV$SESSION S,
GV$PROCESS P,
SYS.OBJ$ O,
GV$SQL Q
WHERE L.SID = S.SID
AND L.INST_ID = S.INST_ID
AND S.INST_ID = P.INST_ID(+)
AND S.PADDR = P.ADDR(+)
AND S.ROW_WAIT_OBJ# = O.OBJ#(+)
AND L.CTIME >= 1
AND S.SQL_ID = Q.SQL_ID(+)
GROUP BY DECODE(LV, 1, 'Holder: ' || S.SID, 'Waiter: ' || S.SID),

S.INST_ID, S.USERNAME, O.NAME, L.TYPE, L.LMODE, L.CTIME, S.STATUS,

S.MACHINE,S.SQL_ID,Q.SQL_TEXT;


Identify SERIAL# of Holder:

To identify the Serial# against the SID of Holder: select action, sid, serial#, seconds_in_wait , status , client_identifier, logon_time , module from v$session where sid=<SID of Holder>; Example: select action, sid, serial#, seconds_in_wait , status , client_identifier, logon_time , module from v$session where sid=321; Kill Database Lock: alter system kill session '<SID>,<SERIAL#>'; alter system kill session '321,6868';


Oracle Golden Gate 21c Microservices Installation

Oracle Golden Gate Microservices Architecture Oracle Golden Gate Microservices Architecture has been introduced in Oracle Golden Gate versio...