Oracle Discoverer holds a set of database tables for each Discoverer EUL being used. In the following queries, the prefix eul_owner should be replaced by the database owner corresponding to the specific EUL in question.

The following reporting statistics may be of interest…

1. Discoverer Reports run in the last year (i.e. since SYSDATE-365):

SELECT qs.qs_doc_name              report_run
,      qs.qs_doc_details           report_detail
,      qs.qs_created_date          date_run
,      fnd.user_name               user_name
FROM   <eul_owner>.eul5_qpp_stats  qs
,      fnd_user                    fnd
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date > SYSDATE - 365
ORDER BY qs.qs_created_date DESC
;

2. Discoverer Report usage by USER:

SELECT COUNT(*)                    num_reports
,      fnd.user_name               user_name
,      ppx.full_name               employee_name
FROM   <eul_owner>.eul5_qpp_stats  qs
,      fnd_user                    fnd
,      per_people_x                ppx
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
AND    ppx.person_id(+) = fnd.employee_id
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date > SYSDATE - 365
GROUP BY fnd.user_name
,      ppx.full_name
ORDER BY COUNT(*) DESC
;

3. Discoverer Report usage by REPORT:

SELECT COUNT(*)                    num_reports
,      qs.qs_doc_name              report_run
,      qs.qs_doc_details           report_detail
FROM   <eul_owner>.eul5_qpp_stats  qs
,      fnd_user                    fnd
,      per_people_x                ppx
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
AND    ppx.person_id(+) = fnd.employee_id
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date > SYSDATE - 365
GROUP BY qs.qs_doc_name
,      qs.qs_doc_details
ORDER BY COUNT(*) DESC
;

4. Discoverer Report ownership and assignment (users/responsibilities):

SELECT DISTINCT a.doc_name             disco_report
,      fu_owner.user_name              owner
,      d.responsibility_key            responsbility
,      fu.user_name
,      ppx.full_name                   employee_name
FROM   <eul_owner>.eul5_documents      a
,      <eul_owner>.eul5_access_privs   b
,      <eul_owner>.eul5_eul_users      c
,      fnd_responsibility              d
,      fnd_user                        fu
,      fnd_user                        fu_owner
,      per_people_x                    ppx
WHERE  a.doc_id = b.gd_doc_id(+)
AND    b.ap_type(+) = 'GD'
AND    b.ap_eu_id = c.eu_id(+)
AND    REPLACE(c.eu_username,'#','') = TO_CHAR(d.responsibility_id(+)) || TO_CHAR(d.application_id(+))
AND    TO_CHAR(fu.user_id(+)) = REPLACE(c.eu_username,'#','')
AND    TO_CHAR(fu_owner.user_id(+)) = REPLACE(a.doc_created_by,'#','')
AND    NVL(fu.end_date(+),SYSDATE + 1) > SYSDATE
AND    NVL(fu_owner.end_date(+),SYSDATE + 1) > SYSDATE
AND    ppx.person_id(+) = fu.employee_id
AND    (   fu_owner.user_name = <user>
       OR  fu.user_name = <user>
       )
ORDER BY a.doc_name ASC
;

The following query will list database locks, but needs to be run as a user with the appropriate access to the v$… and dba_locks tables:

SELECT w.session_id   waiting_session
,      h.session_id   holding_session
,      w.lock_type
--,      h.mode_held
--,      w.mode_requested
,      (
       SELECT module 
       ||     ' :: ' 
       ||     action 
       FROM   v$session 
       WHERE  sid=h.session_id
       )              holding_module_action
,      (
       SELECT module 
       ||     ' :: ' 
       ||     action 
       FROM   v$session 
       WHERE  sid = w.session_id
       )              waiting_module_action
FROM   (
       SELECT /*+ NO_MERGE */ * 
       FROM   dba_locks
       )              w
,      (
       SELECT /*+ NO_MERGE */ * 
       FROM   dba_locks
       )              h
WHERE  ((   (h.mode_held != 'None') 
        AND (h.mode_held != 'Null')
        AND (  (h.mode_requested = 'None') 
            OR (h.mode_requested = 'Null')
            )
        )
        AND ((  (w.mode_held = 'None') 
             OR (w.mode_held = 'Null')
             )
             AND (   (w.mode_requested != 'None') 
                 AND (w.mode_requested != 'Null')
                 )
             )
       )
AND    w.lock_type = h.lock_type
AND    w.lock_id1 = h.lock_id1
AND    w.lock_id2 = h.lock_id2
AND    w.session_id != h.session_id
;