I was recently asked if I knew of a way to get the last accessed date for a workbook. The company were looking to find out which workbooks had not been used recently. So I got to thinking and then it hit me. "So long as the company have not turned off Discoverer's statistical gathering mechanism, I can get this information by querying Discoverer's statistics table".
Here then is the SQL code that you will need. To run it you need to be logged into SQL*Plus as the owner of the EUL:
SELECT
QS.QS_DOC_OWNER USERNAME,
QS.QS_DOC_NAME WORKBOOK,
QS.QS_DOC_DETAILS WORKSHEET,
MAX(QS.QS_CREATED_DATE) LAST_USED
FROM
EUL5_QPP_STATS QS
GROUP BY
QS.QS_DOC_OWNER,
QS.QS_DOC_NAME,
QS.QS_DOC_DETAILS
Oracle ACE Listing
Fortune 500 and the Art of Execution
-
The Fortune 500 Companies 2018 rankings came out last week, and browsing
the list, the following random thoughts struck me about the list and the
technolo...
6 years ago
6 comments:
This information can also be found using the EUL workbooks that ship with Discoverer.
There is a small setup routine to access these workbooks, run a SQL script as the EUL owner and import a business area from a shipped .eex file and you are ready to go.
The shipped workbooks include an EUL query statistics workbook that contains a lot of information on workbooks that have been run, when and by whom.
Check out Ch19 of the Discoverer Admin Guide (v.10.1.2) for more details.
Mike Durran
Hi Mike
It's nice to know that someone is actually reading my posts!
You are absolutely correct and this information was also passed to my client. However, the person concerned did not have the authority to execute SQL scripts on the database so he wondered if there was a quick way to do it, perhaps in a Custom folder, until such time as he gained approval for the DBA to do it.
If anyone can gain access to the EUL5 Status workbooks I would always recommend doing so, because, as you say, they are packed with additional information.
Hi Michael.
Isn't it funny?
2hrs ago I read this post...
...and now I am in situation when I need to quickly find this kind of information and I don't have EUL5 status workbooks installed in the EUL I am interested in.
Thanks for simple but very useful SQL.
Jakub.
Mike,
I'm new to Discoverer...thrown in as an Admin no training...nothing. We have performance issues (it resides on the same server as GL. Users had free reign and after our upgrade to Oracle 10g and SUN 10, we're noticing some bad performance.
I have installed the EUL5 Workbooks. I did notice an error in the EUL5.sql script (couldn't find a table it was trying to drop) but I can see the EUL5 workbooks as the EUL owner.
I need to quickly identify the worst performing SQL statements, and an analysis on how long they took, how often they ran, etc..
I ran the Query Workbook Statistics and it timed out after 30 minutes. What is the normal time for these queries to run? 30 - 60 minutes a reasonable time to execute?
Appreciate your help
Stan
Hi Stan
Drop me a line on michael@learndiscoverer.com and I will try and help. If this workbook was taking that long then this means that you must have a lot of statistics, which also means you have a lot of workbooks and / or users.
As I say, drop me an email.
Michael
Mike, i have got the EUL5 Status workboot installed; am not able to figure how to find who ran a report last though. Appreciate your help. Thanks.
Post a Comment