Description:
Following sql script is used to find the Application Express users , who all are logged in previous days or specific period (months , years) .
Steps:
1.Login as system or sys schema in your database.
2.Check which apex user details using following query.
SQL> select username , account_status from dba_users where username like ‘%APEX%’;
USERNAME ACCOUNT_STATUS
—————– —————————
APEX_050000 OPEN
3.Use the following query to find the Apex user login details.
select WORKSPACE,APEX_USER,APPLICATION_NAME,trunc(MIN_VIEW_DATE) “DATE”,count(trunc(MIN_VIEW_DATE)) cnt
from <Apex username>.APEX_USER_LOGIN_DETAILS
where trunc(MIN_VIEW_DATE) >= ‘<FROM DATE>’
and trunc(MAX_VIEW_DATE) <= ‘<TO DATE>’
AND APEX_USER NOT LIKE ‘%ADMIN’
AND APEX_USER NOT IN (‘nobody’)
–AND WORKSPACE IN (‘PMS’,’SCOTS’)
group by WORKSPACE,apex_user,application_name,trunc(MIN_VIEW_DATE)
ORDER BY apex_user;
Thank you.
