I’ve just been asked to come up with a quick script for our DBAs to let them know who has been using the system recently. They want something that they can use to pick up the recent eBS users and their email addresses, so that they can let them know when the system is about to be bounced or shut down.

So, here’s the SQL:

SELECT DISTINCT ppf.full_name
, fu.user_name
, NVL(ppf.email_address,fu.email_address) email_address
FROM per_people_f ppf
, fnd_user fu
, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppf.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('SYSADMIN', 'GUEST')
/

Note, it will only pick up recent eBS users – it doesn’t look at database users who are logged in, so if there is other access to the system then you need to be aware that this isn’t going to be a definitive list. Good starter for ten though.

This entry was posted on Wednesday, October 1st, 2008 at 3:09 pm and is filed under Oracle, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.