Useful shortcuts for vi editor

Thursday, 12 January 2017

How to kill inactive session for non-background oracle processes?

At first, check the sessions
SQL>
SELECT DISTINCT spid, b.username, osuser
    FROM sys.dba_ddl_locks a, sys.v_$session B, v$process c
   WHERE     B.SID = a.session_id
         AND owner NOT IN ('SYS',
                           'WMSYS',
                           'CTXSYS',
                           'DBSNMP')
       AND B.TYPE <> 'BACKGROUND'
       AND B.status = 'INACTIVE'
         AND (c.addr = b.paddr)
ORDER BY username, osuser;

Kill the session
SQL>
SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;' AS KILL_THEM_ALL
  FROM sys.dba_ddl_locks a, sys.v_$session B
 WHERE     B.SID = a.session_id
       AND owner NOT IN ('SYS',
                         'WMSYS',
                         'CTXSYS',
                         'DBSNMP')
       AND B.TYPE <> 'BACKGROUND'
       AND B.status = 'INACTIVE'