Query to find the number of users log in to Oracle EBS in last 1 hour

Below scripts can be utilized to find the number and details of EBS users that were on the system in the last 1 hour. The duration can be modified by changing the hour to some other duration.

SELECT COUNT(xs.user_id) "users"
FROM icx_sessions xs,
fnd_user fu
WHERE xs.last_connect > SYSDATE - 1/24
AND xs.user_id != '-1'
AND fu.USER_ID = xs.USER_ID;

select count(*)
from icx_sessions s, fnd_user u, fnd_responsibility_vl r, fnd_form_functions_vl f, fnd_nodes n
where u.user_id(+) = s.user_id
AND f.function_id(+) = s.function_id
AND r.responsibility_id(+) = s.responsibility_id
AND n.node_id(+) = s.node_id
AND s.disabled_flag != 'Y'
and s.pseudo_flag = 'N'
and (s.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,s.limit_time, 0,s.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate
and s.counter < s.limit_connects;

select u.user_name, r.responsibility_name, f.user_function_name, s.function_type,
to_char(s.first_connect, 'DD-MON HH24:MI:SS') "First Connect",
to_char(s.last_connect, 'DD-MON HH24:MI:SS') "Last Connect", n.node_name
from icx_sessions s, fnd_user u, fnd_responsibility_vl r, fnd_form_functions_vl f, fnd_nodes n
where u.user_id(+) = s.user_id
AND f.function_id(+) = s.function_id
AND r.responsibility_id(+) = s.responsibility_id
AND n.node_id(+) = s.node_id
AND s.disabled_flag != 'Y'
and s.pseudo_flag = 'N'
and (s.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,s.limit_time, 0,s.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate
and s.counter < s.limit_connects;

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Query to find the number of users log in to Oracle EBS in last 1 hour

Related posts: