PLSQL Queries for User Sessions

http://dbissues.blogspot.com/2017/05/plsql-queries-for-user-sessions.html

1: Use this SQL statement  to check the query of current/Active users

=============================================================
select fnd.user_name, icx.responsibility_application_id, icx.responsibility_id, frt.responsibility_name,
icx.session_id, icx.first_connect,
icx.last_connect,
DECODE ((icx.disabled_flag),'N', 'ACTIVE', 'Y', 'INACTIVE') status
from
fnd_user fnd, icx_sessions icx, fnd_responsibility_tl frt
where
fnd.user_id = icx.user_id
and icx.responsibility_id = frt.responsibility_id
and icx.disabled_flag <> 'Y'
and trunc(icx.last_connect) = trunc(sysdate)
order by icx.last_connect;

2:  Use this SQL statement to count number of concurrent_users connected to Oracle apps:

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

select count(distinct d.user_name) from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d

where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id

and (d.user_name = ‘USER_NAME’ OR 1=1)

3:  Use this SQL statement to count number of users connected to Oracle Apps in the past 1 hour.

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

select count(distinct user_id) “users” from icx_sessions where last_connect > sysdate – 1/24 and user_id != ‘-1’;

4:  Use this SQL statement to get number of users connected to Oracle Apps in the past 1 day.

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

select count(distinct user_id) “users” from icx_sessions where last_connect > sysdate – 1 and user_id != ‘-1’;

5:  Use this SQL statement to get number of users connected to Oracle Apps in the last 15 minutes.

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

select limit_time, limit_connects, to_char(last_connect, ‘DD-MON-RR HH:MI:SS’) “Last Connection time”, user_id, disabled_flag from icx_sessions where last_connect > sysdate – 1/96;

6: How do we know how many users are connected to Oracle Applications

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

select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility
from fnd_user fu, fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect like sysdate;

7: Count Number of concurrent_users in Oracle apps?

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

select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);

8:How to find out which users are logged on to an Apps instance :-
====================================================

FND_USER table stores the details of all the end users. If we give this query:

select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');

9: You can check the number of user session for the application using ICX_SESSIONS table. Use below query for checking the number of user sessions.

=================================================================================
select ((select sysdate from dual)),(select  ‘ user sessions : ‘ || count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != ‘Y’
and PSEUDO_FLAG = ‘N’
and (last_connect + decode(FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’)/60)/24) > sysdate
and counter < limit_connects) from dual