errors creating a functional based index 3

[Jeff6776]

I've looked up the error message asscociated with ora-01031 and it relates to a user's password changes. Anyone have ideas on how to get a functional based index working.

1 CREATE INDEX table_field_caps_IDX ON table(UPPER(field))
2 TABLESPACE index_tablespace
3 PCTFREE 10
4* STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0 )
5 /
CREATE INDEX table_field_CAPS_IDX ON table(UPPER(field))
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

[sem]

You must be granted the CREATE INDEX and QUERY REWRITE system privileges or CREATE ANY INDEX and GLOBAL QUERY REWRITE to create functional based indexes in your or any schema respectively.

 

[karluk]

As a start, please make sure that you have enough privileges to create the index. The following description of the privileges needed is an extract from the Oracle8i SQL Reference Release 3 (8.1.7) Part Number A85397-01.

To create a function-based index in your own schema on your own table, in addition to the prerequisites for creating a conventional index, you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege. In both cases, the table owner must also have the EXECUTE object privilege on the function(s) used in the function-based index. In addition, in order for Oracle to use function-based indexes in queries, the QUERY_REWRITE_ENABLED parameter must be set to true, and the QUERY_REWRITE_INTEGRITY parameter must be set to trusted.