0

I'm the DBA for a set of Oracle databases. I have developers who write PL/SQL queries and we are currently looking at tuning these queries. My developers are trying to run Explain Plans through SQL Developer to do the tuning but are hitting an error as they haven't been granted the SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY privileges. Can I please get some advice on how best to proceed, as I have read in numerous places that granting SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY can give access beyond the principles of least privilege?

Thanks

Sean

SeanGaff
  • 93
  • 11
  • Also, for information, we are running Oracle 19C in non-cloud. – SeanGaff Sep 09 '22 at 13:13
  • 1
    Two things - 1. explain plans are OK but not as ideal as using DBMS_XPLAN. 2. what are their queries hitting, schema objects or data dictionary objects? – thatjeffsmith Sep 09 '22 at 14:32
  • Hi, primarily schema objects. Primarily looking at tuning SELECT statements that have multiple joins in them – SeanGaff Sep 12 '22 at 11:34
  • then there should be no need SELECT CATALOG..ANY DICTIONARY – thatjeffsmith Sep 12 '22 at 12:55
  • Hi, I've just discovered that the error they were getting was because they were trying to run the Autotrace function rather than an explain plan. However, they are getting an 'ORA-01031: insufficient privileges' error when executing the Explain Plan function in SQL Developer and I have granted full SELECT on all the underlying objects that are called in the query. Do I need to build a plan table for them? – SeanGaff Sep 13 '22 at 11:09

0 Answers0