Why can I execute this query but not see its execution plan?

Question

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.

1. I do have privileges on the table accessed by SQL as the query runs fine.
2. By output table I assume it means dbms_xplan.display which 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?

Answer 1

I do have privileges on the table accessed by SQL as the query runs fine.

And that is a false assumption. Just because you can query a view, it doesn't necessarily mean you have privileges on the underlying tables. Anyone can query the view ALL_TABLES, but a regular user doesn't have the privileges to query the underlying tables such as OBJ$ or TAB$.

This is how it works, and it is really easy to reproduce. Create two users, one with a table and view, then grant SELECT on that view to another user:

SQL> grant create session, create table, create view to u1 identified by u1;

Grant succeeded.

SQL> grant create session to u2 identified by u2;

Grant succeeded.

SQL> create table u1.t1(id number);

Table created.

SQL> create view u1.v1 as select * from u1.t1;

View created.

SQL> grant select on u1.v1 to u2;

Grant succeeded.

Now connect as u2, and try to select from the view:

SQL> connect u2/u2
Connected.

SQL> select * from u1.v1;

no rows selected

No problem. Now try to explain:

SQL> explain plan for select * from u1.v1;
explain plan for select * from u1.v1
                                  *
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view

Answer 2

In the Oracle documentation it says that you mist have an EXPLAIN_PLAN table to store the information:

Before issuing an EXPLAIN PLAN statement, you must have a table to hold its output. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. Use the SQL script UTLXPLAN.SQL to create the PLAN_TABLE in your schema. The exact name and location of this script depends on your operating system. On Unix, it is located in the $ORACLE_HOME/rdbms/admin directory.

Run this script and you will be able to use the EXPLAIN PLAN.

Answer 3

Make sure you have the UPDATE privilege on the underlying tables. As I pointed out on stackoverflow, the SELECT privilege may not be enough

https://stackoverflow.com/a/74706625/12884507