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 and AI Architect with over 19 years in Oracle Applications, Databases, and Cloud ecosystems. My work spans large-scale, global projects where I’ve designed and optimized solutions across both Oracle and non-Oracle stacks—on-prem and in the cloud. Lately, my focus has shifted toward integrating AI into enterprise architectures, leveraging GenAI, automation, and data intelligence to drive performance, resilience, and modernization. I’m passionate about exploring emerging AI tools, agentic workflows, and practical use cases—and I'm fortunate that my role gives me space to experiment and share my learnings.

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

Related posts: