Friday, March 17, 2017

Important Queries to check Oracle EBS users logged

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);

select * 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)

select last_connect, usr.user_name, resp.responsibility_key, function_type, icx.*
  from apps.icx_sessions icx
  join apps.fnd_user usr on usr.user_id=icx.user_id
  left join apps.fnd_responsibility resp on resp.responsibility_id=icx.responsibility_id
  where last_connect>sysdate-nvl(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),30)/60/24
and disabled_flag != 'Y' and pseudo_flag = 'N'
AND USER_NAME <>'GUEST'

select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1/24 and user_id != '-1';

select * from v$license;

No comments:

Post a Comment