2 Replies Latest reply on Apr 9, 2020 5:25 AM by Bommi

    Tables for Oracle Discoverer

    Bommi

      Hi Experts,

       

      I am aware that Oracle stopped supporting Oracle Discoverer. But still we are using (And of course soon going to stop using this)

       

      We want to know the table or query to find out the Reports that are being ran in last 6 months/ 1 Year/ 2 Years.

      I tried to use the EUL5_QPP_STATS table. But it is showing not more than 10 reports that are ran in last 2 years. My self, ran almost 15 reports in last 1 month. All those are not showing. Also, the table contains only the reports that are ran by using user: RPT_OWNER only (I am not sure if this the Oracle provided standard user or any thing else). But, we are having many users created and many time at clients side, they are running reports by using their own users.

       

      So, how can we find out all the reports that are ran in last 2 years (even when ran using different user user logins)?

      Also, why EUL5_QPP_STATS is showing very less number of reports? Is the table name I am checking is wrong?

       

      Please help me if anyone has any idea on this.

       

      Thanks in Advance,

      Bommi

        • 1. Re: Tables for Oracle Discoverer
          Alexander Pavlik

          Hi Bommi,

           

          You're using the correct table

           

          EUL5_QPP_STATS is populated by Discoverer Desktop or Discoverer plus with a history of worksheet query execution statistics. The information is written to the table upon exit from above applications.

           

          In case you're using Discoverer Desktop to ensure that the statistics are written, a registry setting for Discoverer parameter QPPCreateNewStats needs to be added or adjusted

          If you're using Discoverer plus using a web browser, a configuration file needs to be edited on the Discoverer server side (usually Linux).

          Please check if the following instructions help you. It has instructions for the both cases. Here's the extract:

           

          To ensure that the statistics are written, a registry setting for Discoverer parameter QPPCreateNewStats needs to be added or adjusted, as described in Oracle’s Discoverer Administrator Guide. Open the Windows registry editor.

          Go to the following path (Replace Discoverer 11 with your version):

          Computer\HKEY_CURRENT_USER\Software\Oracle\Discoverer 11\Database

          If the setting for key QPPCreateNewStats already exists, ensure that it’s value is set to ‘1’.

          If the key does not exist, create it by using Edit > New > DWORD (32-bit) Value:

          Discoverer server configuration file

          For Discoverer plus and viewer users, following actions need to be performed which are also outlined in the following Oracle document, section “9.4 How to set default user preferences for all users”.


          Open pref.txt file under $ORACLE_INSTANCE/config/PreferenceServer/$DISCO_COMP_NAME

          nano /u01/disco/middleware/asinst_1/config/PreferenceServer/Discoverer_asinst_1/pref.txt

          Update or add required parameters and save the file:

          SaveLastUsedParamValue = 1 QPPCreateNewStats = 1

          Run script applypreferences.sh under $ORACLE_INSTANCE/Discoverer/$DISCO_COMP_NAME/util

          /u01/disco/middleware/asinst_1/Discoverer/Discoverer_asinst_1/util/applypreferences.sh

          Restart the Discoverer_[instance_name]  ias-component:

          $ORACLE_INSTANCE/bin/opmnctl stopproc ias-component=Discoverer_asinst_1 $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=Discoverer_asinst_1 $ORACLE_INSTANCE/bin/opmnctl status

           

           

          So unfortunately looks like the above configuration hasn't been done so far and the statistics for the previous years won't be available.

           

          After the configuration is performed the following script can be used to show Discoverer worksheet access statistics from table eul5_qpp_stats. You just need to replace &eul bind variable to the name of EUL WITH a dot at the end. Example: EUL_US.

           

          select distinct
          eqs.qs_id id,
          xxen_util.dis_user_name(eqs.qs_created_by) user_name,
          eqs.qs_doc_name workbook,
          ed.doc_developer_key workbook_identifier,
          eqs.qs_doc_details sheet,
          xxen_util.client_time(eqs.qs_created_date) start_date,
          xxen_util.time(eqs.seconds) time,
          eqs.seconds,
          eqs.qs_num_rows row_count,
          &object_columns
          length(eqs.qs_object_use_key)-length(translate(eqs.qs_object_use_key,'x.','x'))+1 folder_count,
          xxen_util.meaning(case when eqs.qs_object_use_key like '%.%' then 'Y' end,'YES_NO',0) multiple_flag,
          eqs.qs_object_use_key use_key
          from
          (
          select
          trim(regexp_substr(eqs.qs_object_use_key,'[^.]+',1,rowgen.column_value)) obj_id,
          greatest(nvl(eqs.qs_act_cpu_time,0),nvl(eqs.qs_act_elap_time,0)) seconds,
          eqs.*
          from
          &eul.eul5_qpp_stats eqs,
          table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'.')+1)) rowgen
          where
          1=1
          ) eqs,
          &eul.eul5_objs eo,
          (
          select
          ed.doc_name,
          fu.user_name,
          ed.doc_developer_key
          from
          &eul.eul5_documents ed,
          &eul.eul5_eul_users eeu,
          fnd_user fu
          where
          ed.doc_eu_id=eeu.eu_id and
          case when eeu.eu_username like '#%' and eeu.eu_username not like '#%#%' then to_number(substr(eeu.eu_username,2)) end=fu.user_id
          ) ed
          where
          2=2 and
          translate(eqs.obj_id,'x0123456789','x') is null and
          eqs.obj_id=eo.obj_id(+) and
          eqs.qs_doc_name=ed.doc_name(+) and
          eqs.qs_doc_owner=ed.user_name(+)
          order by
          eqs.qs_id desc
          

           

          Regards,

          Alex

          1 person found this helpful
          • 2. Re: Tables for Oracle Discoverer
            Bommi

            Hi Alex,

             

            Very thanks for your valuable inputs

            Will check this and let you know the results

             

             

            Thanks,

            Bommi