2 Replies Latest reply on Jun 1, 2018 11:47 AM by thatJeffSmith-Oracle

    Running Schema Specific SQL Developer Reports question.

    Manoj Dixit



      I have SQL Developer installed on my desktop.


      I have been working on data mart/warehouse reporting tools. I quite like monitoring SQL queries being run when reports are executed. In my current job, the data mart schema is part of a larger application and the DBAs have said that enabling running the reports available in the SQL Developer tool (please see below the reports I am interested in) will expose the whole DB related information. Also, they have expressed that running these reports will require DBA access (which I read it as "my role being a developer, sorry" . Btw, no I am not trying to do a DBA job, I am trying to focus on SQL query enhancements at the application level.). The DBAs have, however, kindly taken initiative to export the data that I am interested in and send it to me.


      I enjoy reading materials related to SQL query tuning. When I read something interesting, I would like to see how my current application is behaving in terms of the plan and the actual execution. However, except the explain plan, I can't see the actual execution as when I run some of the reports, I get a message "ORA-00942: table or view does not exist" which I assume is related to no grant to read data from the underlying data dictionary table(s)/view(s). The same message is displayed, for some reports, irrespective of running a SQL query, for example,Statistics.


      The reports that I am interested to run are:


      Under Database Administration:-

      All Tables >> Statistics - I would like to know my data mart schema specific statistics.

      Locks >> Both or Locks by User.

      Sessions >> Sessions by Username (I am hoping that this report shows how the query is running as opposed to the explain plan).

      Storage >> Free Space.

      Top SQL >> All.


      I am looking for ways to amicably solve the issue. Any ideas for me or the DBAs, please? Is there any way that along with the DBAs, specific schema too can run the reports for that specific schema only?


      The last option for me is to agree with the option the DBAs have given (that is exporting the report's data and sending it out to me) but in that case, a user can contact me when a particular report is running slow, but the DBAs may be busy with some high priority task. I may miss how a query is behaving at that particular point in time. Here, I am assuming that once the query is run, Oracle does not store how it ran in the past.


      If you have any questions, please let me know.


      Thanks and regards,