Posts Tagged ‘Check Number Of Oracle Users With Different Queries’

Check Number Of Oracle Users With Different Queries

June 17th, 2016, posted in Oracle Queries
Share

Query # 1 :

desc v$license
Name Null? Type
----------------------------------------- -------- ----------------
SESSIONS_MAX NUMBER
SESSIONS_WARNING NUMBER
SESSIONS_CURRENT NUMBER
SESSIONS_HIGHWATER NUMBER
USERS_MAX NUMBER
CPU_COUNT_CURRENT NUMBER
CPU_CORE_COUNT_CURRENT NUMBER
CPU_SOCKET_COUNT_CURRENT NUMBER
CPU_COUNT_HIGHWATER NUMBER
CPU_CORE_COUNT_HIGHWATER NUMBER
CPU_SOCKET_COUNT_HIGHWATER NUMBER

 

Query # 2 :

select sessions_current from v$license;

 

Query # 3 :

select SESSIONS_CURRENT,SESSIONS_HIGHWATER,CPU_COUNT_CURRENT,CPU_COUNT_HIGHWATER from v$license;

 

Query # 4 :

SELECT USERNAME FROM DBA_USERS

 

Query # 5 :

SELECT distinct user_id from FND_LOGINS

 

Query # 6 :

SELECT distinct user_id from icx_sessions

 

Query # 7 :

SELECT distinct user_id from FND_USER

 

Query #8:


select application_name,responsibility_name,
security_group_name, user_name,
greatest(u.start_date, ur.start_date, r.start_date) start_date,
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)),
nvl(ur.end_date, nvl(u.end_date, r.end_date)),
nvl(r.end_date, nvl(u.end_date, ur.end_date))) end_date
from fnd_user u,fnd_user_resp_groups ur,
fnd_responsibility_vl r,fnd_application_vl a, 
fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date  sysdate
and u.start_date  sysdate
and r.start_date  sysdate
and ur.security_group_id = s.security_group_id
order by application_name,responsibility_name,security_group_name, user_name

 

Query # 9 :


select application_name,responsibility_name,
security_group_name, user_name,
greatest(u.start_date, ur.start_date, r.start_date) start_date,
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)),
nvl(ur.end_date, nvl(u.end_date, r.end_date)),
nvl(r.end_date, nvl(u.end_date, ur.end_date))) end_date
from fnd_user u,fnd_user_resp_groups ur,
fnd_responsibility_vl r,fnd_application_vl a, 
fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date  sysdate
and u.start_date  sysdate
and r.start_date  sysdate
and ur.security_group_id = s.security_group_id
order by application_name,responsibility_name,security_group_name, user_name

 

Share