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?
I do have privileges on the table accessed by SQL as the query runs
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
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
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