Kill session on EBS environment

In Oracle EBS environment, single user has multiple session in all most every moment of its work. So if you want to see details about it or has to kill all of them, finding out is crucial for that task.

Next query can give you answer about current users sessions:

/* ---------------------------------------------------------------------------
 Filename: apps_sess.sql
 CR/TR#  :

 Purpose : Shows all active session on Oracle EBS 11.5.10.2 (10gR2)

 Date    : 19.08.2007.
 Author  : Damir Vadas

 Remarks : Run as APPS or privileged user

 Changes (DD.MM.YYYY, Name, CR/TR#):
--------------------------------------------------------------------------- */

SELECT DISTINCT * FROM (
        select
              usr.user_name user_name
             ,ses.sid||','||ses.serial# sid_serial
        from
             apps.icx_sessions i
            ,apps.fnd_logins l
            ,apps.fnd_appl_sessions a
            ,apps.fnd_user usr
            ,gv$process v
            ,gv$session ses
        where i.disabled_flag = 'N'
          and i.login_id = l.login_id
          and l.end_time is null
          and i.user_id = usr.user_id
          and l.login_id = a.login_id
          and a.audsid = ses.audsid
          and l.pid = v.pid
          and l.serial# = v.serial#
    UNION
        select
           usr.user_name
          ,ses.sid||','||ses.serial#
        from
           apps.fnd_logins l
          ,apps.fnd_login_responsibilities r
          ,apps.fnd_user usr
          ,gv$process v
          ,gv$session ses
        where l.end_time is null
          and l.user_id = usr.user_id
          and l.pid = v.pid
          and l.serial# = v.serial#
          and v.addr = ses.paddr
          and l.login_id = r.login_id(+)
          and r.end_time is null
          and r.audsid = ses.audsid
    UNION
        select
           usr.user_name
          ,ses.sid||','||ses.serial#
        from
           apps.fnd_logins l
          ,apps.fnd_login_resp_forms f
          ,apps.fnd_user usr
          ,apps.fnd_form_tl frm
          ,apps.fnd_form_functions ff
          ,gv$process v
          ,gv$session ses
        where l.end_time is null
          and l.user_id = usr.user_id
          and l.pid = v.pid
          and l.serial# = v.serial#
          and v.addr = ses.paddr
          and l.login_id = f.login_id(+)
          and f.end_time is null
          and f.form_id = frm.form_id(+)
          and f.form_appl_id = frm.application_id(+)
          and f.audsid = ses.audsid
          and ff.form_id = frm.form_id
    UNION
        select
          fu.user_name
         ,vs.SID || ',' || vs.serial#
        FROM
           APPS.fnd_concurrent_requests cr,
           v$process vp,
           v$session vs,
           apps.fnd_user fu
        WHERE 
               cr.phase_code <> 'I'
           AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
           AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'I', 'C')
           AND cr.oracle_process_id = vp.spid (+)
           AND cr.oracle_session_id = vs.audsid (+)
           AND fu.user_id = cr.requested_by
           AND vs.sid is not null
)
ORDER BY 1,2
;

If you want to kill them then you will have to add sligthly different start of the script:

SELECT DISTINCT 'ALTER SYSTEM DISCONNECT SESSION '''|| sid_serial || ''' IMMEDIATE ;' kill_me FROM (
select
      usr.user_name user_name
     ,ses.sid||','||ses.serial# sid_serial
from
     apps.icx_sessions i
    ,apps.fnd_logins l
...

...
   AND cr.oracle_process_id = vp.spid (+)
   AND cr.oracle_session_id = vs.audsid (+)
   AND fu.user_id = cr.requested_by
   AND vs.sid is not null
)
ORDER BY 1
;

Please keep in mind that here is intentionally excluded where part:

usr.user_id >= 1110

which doesn't exclude some system and priority users, so in real case you may add that additional condition in every sub query.

Result will be something like:

kill_me
------------------------------------------------------------------------------------
ALTER SYSTEM DISCONNECT SESSION '1006,8580' IMMEDIATE ;                                                                       
ALTER SYSTEM DISCONNECT SESSION '1012,16446' IMMEDIATE ;                                                                      
ALTER SYSTEM DISCONNECT SESSION '1063,16467' IMMEDIATE ;                                                                      
ALTER SYSTEM DISCONNECT SESSION '1075,12361' IMMEDIATE ;                                                                      
...

...
ALTER SYSTEM DISCONNECT SESSION '1689,13000' IMMEDIATE ;                                                                      
ALTER SYSTEM DISCONNECT SESSION '1699,45171' IMMEDIATE ;                                                                      
ALTER SYSTEM DISCONNECT SESSION '955,3624' IMMEDIATE
;

While executing previous commands you get any kind of error (I have seen such a cases) repeat it with kill session command!
For an example:

ALTER SYSTEM KILL SESSION '1699,45171' IMMEDIATE ;

Reducing query output

Previous example gives you disconnect session command for all active sessions. This situation is in praxis very rare.
In real life you will have to reduce it to some specific user (or group of users). For that you have to add in all of sub queries next where condition:

upper(user_name) in ('USER1','USER2')

In this example this will reduce output for only 2 users-USER1 and USER2.

"Temporary disable APPS users" story

If you have read my topic Temporary disable APPS users, there might be a need to terminate running session from all users except some of them. In that topic I use xxhy_table_ch table which holds the list of users who should not be killed. According that this script should look like:

SELECT DISTINCT 'ALTER SYSTEM DISCONNECT SESSION '''|| sid_serial || ''' IMMEDIATE ;' kill_me FROM (
    select
          usr.user_name user_name
         ,ses.sid||','||ses.serial# sid_serial
    from
         apps.icx_sessions i
        ,apps.fnd_logins l
        ,apps.fnd_appl_sessions a
        ,apps.fnd_user usr
        ,gv$process v
        ,gv$session ses
    where i.disabled_flag = 'N'
      and i.login_id = l.login_id
      and l.end_time is null
      and i.user_id = usr.user_id
      and l.login_id = a.login_id
      and a.audsid = ses.audsid
      and l.pid = v.pid
      and l.serial# = v.serial#
      and usr.user_name not in (select c1 from xxhy_table_ch)
      and usr.user_id >= 1110
UNION
    select
       usr.user_name
      ,ses.sid||','||ses.serial#
    from
       apps.fnd_logins l
      ,apps.fnd_login_responsibilities r
      ,apps.fnd_user usr
      ,gv$process v
      ,gv$session ses
    where l.end_time is null
      and l.user_id = usr.user_id
      and l.pid = v.pid
      and l.serial# = v.serial#
      and v.addr = ses.paddr
      and l.login_id = r.login_id(+)
      and r.end_time is null
      and r.audsid = ses.audsid
      and usr.user_name not in (select c1 from xxhy_table_ch)
      and usr.user_id >= 1110
UNION
    select
       usr.user_name
      ,ses.sid||','||ses.serial#
    from
       apps.fnd_logins l
      ,apps.fnd_login_resp_forms f
      ,apps.fnd_user usr
      ,apps.fnd_form_tl frm
      ,apps.fnd_form_functions ff
      ,gv$process v
      ,gv$session ses
    where l.end_time is null
      and l.user_id = usr.user_id
      and l.pid = v.pid
      and l.serial# = v.serial#
      and v.addr = ses.paddr
      and l.login_id = f.login_id(+)
      and f.end_time is null
      and f.form_id = frm.form_id(+)
      and f.form_appl_id = frm.application_id(+)
      and f.audsid = ses.audsid
      and ff.form_id = frm.form_id
      and usr.user_name not in (select c1 from xxhy_table_ch)
      and usr.user_id >= 1110
UNION
    select
      fu.user_name
     ,vs.SID || ',' || vs.serial#
    FROM
       APPS.fnd_concurrent_requests cr,
       v$process vp,
       v$session vs,
       apps.fnd_user fu
    WHERE 
           cr.phase_code <> 'I'
       AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
       AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'I', 'C')
       AND cr.oracle_process_id = vp.spid (+)
       AND cr.oracle_session_id = vs.audsid (+)
       AND fu.user_id = cr.requested_by
       AND vs.sid is not null
       AND usr.user_name not in (select c1 from xxhy_table_ch)
       AND usr.user_id >= 1110
)
ORDER BY 1
;

Hereby, shown SQL is fully non RAC version. For extended information how to kill session on RAC environment, please read the whole "How to" on my previous blog Blocking session ... detect, unblock, notify.

As you can see with this part of story Temporary disable APPS users is finished and completed. Once again here is included usr.user_id >= 1110 condition which was mentioned in that blog topic as important.