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

explainoracle

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?

Best Answer

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
Related Question