0

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?

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
0

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.

  • Do you mean dbms_xplan.display? Apparently I have it already as other EXPLAIN statements are running just fine. – Kshitiz Sharma Jun 3 '16 at 12:46
  • I have always used the default table. Never this dbms_xplan.display. Do you have rights to this object (guess so since you say that other EXPLAIN PLAN statements work)? The error should not come from your select count(*) statement since all_tables only returns tables where you have access to (unless you lack the select privilege). – Marco Jun 3 '16 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.