Consider the following query:
SELECT table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' ||owner ||'.' ||table_name)),'/ROWSET/ROW/C')) AS COUNT FROM all_tables WHERE UPPER(owner) = UPPER('my_schema');
This query runs just fine and displays output. But doing EXPLAIN on this query produces:
ORA-01039: insufficient privileges on underlying objects of the view
As per the Oracle documentation:
You must have the privileges necessary to execute the SQL statement for which you are determining the execution plan. If the SQL statement accesses a view, then you must have privileges to access any tables and views on which the view is based.
To examine the execution plan produced by an EXPLAIN PLAN statement, you must have the privileges necessary to query the output table.
- I do have privileges on the table accessed by SQL as the query runs fine.
- By output table I assume it means
dbms_xplan.displaywhich is to store calculated plan. I think that I have privilege on that aswell as following runs fine:
EXPLAIN PLAN FOR SELECT * FROM myschema.mytable; SELECT * FROM TABLE(dbms_xplan.display);
What's missing here?