What privileges are needed to run EXPLAIN PLAN FOR CREATE INDEX?

Posted on

Question :

My user can EXPLAIN PLAN FOR any query I like, but not for CREATE INDEX. I need to do that in order to estimate a suggested index’s size, and using sysdba is out of question. What privileges should I ask for? This is Oracle

SQL> explain plan for create index INDEXNAME on SOMEONE.SOMETABLE("FLD1","FLD2");
explain plan for create index INDEXNAME on SOMEONE.SOMETABLE("FLD1","FLD2")
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> explain plan for select * from SOMEONE.SOMETABLE order by fld1 desc;



Answer :

The problem is most likely in you not having sufficient privileges to create the index in question — nothing to do with EXPLAIN PLAN itself.

To quote the manual, “To create an index in another schema, you must have the CREATE ANY INDEX system privilege”.

Leave a Reply

Your email address will not be published. Required fields are marked *