I am an Oracle DBA, but I don't have any experience writing Discoverer reports. We have hundreds of locked accounts that were
owned by people who no longer work at my company. I am trying to see if any of those reports are being used. I would like to both
identify which reports haven't been run in years and locked users whose reports have not been run in years. I could then clean up
old reports and clean up old users.
This is the query that I have so far. But from this I can't tell when the last time a users reports where run.
Thanks,
SELECT usr.username, usr.account_status,
eul.cnt,
TO_CHAR(eul.DOC_UPDATED_DATE, 'DD-MON-YYYY') report_update_date,
TO_CHAR(usr.lock_date, 'DD-MON-YYYY') lock_date
FROM dba_users usr
INNER JOIN
( SELECT doc_created_by, COUNT(*) cnt,
MAX(DOC_UPDATED_DATE) DOC_UPDATED_DATE
FROM euladm.eul5_documents
GROUP BY doc_created_by ) eul
ON usr.username = eul.doc_created_by
WHERE usr.account_status = 'LOCKED'
ORDER BY eul.DOC_UPDATED_DATE, usr.username;