Simple solutions using PLSQL

Scenario1:

While trying to rebuild indexes dynamically using a stored procedure, I constantly get the error ORA-01030 Insufficient Privileges.
Below are some important points to make sure we don’t deviate from the actual problem

The stored procedure is owned by the schema from where I am executing it
I am trying to rebuild the indexes owned by the same schema
If I try to execute the below using an anonymous PLSQL block, the statement executes fine

Sample Code:

OPEN get_indexes_validate;

LOOP
FETCH get_indexes_validate
INTO ph_indexes;

EXIT WHEN get_indexes_validate%NOTFOUND;

Print ('Processing alter index '
ph_indexes
' rebuild online'
);

EXECUTE IMMEDIATE ( 'alter index '
ph_indexes
' rebuild online'
);

Print ('1.b 'SQLERRM);

END LOOP;

Solution & Explanation

Please note that during the compilation or execution of PLSQL stored code the ROLES (Usually CONNECT, RESOURCE). associated to a schema get disabled and hence the DDL or any privileges associated with those roles also get disabled during runtime of a stored code using dynamic SQL.

Extension of the problem:

Even after granting ‘ALTER ANY INDEX’ and ‘CREATE ANY INDEX’ to the schema, I still get the error ORA-01030 Insufficient Privileges.

Extended Solution & Explanation
The problem lies with your 'online' keyword as this causes Oracle to create an IOT (Index Organized Table) "under the covers" to support the index rebuild. You can either remove it or alternatively grant the user running the procedure explicit "CREATE ANY TABLE" and "CREATE ANY INDEX" privileges.