Re: Privileges for creating indexes depends on type of index?

On Jun 18, 4:01 pm, dean <deanbrow..._at_yahoo.com> wrote:
> Hello all,
>
> I have a special kind of index (selective uniqueness index, as
> described in Tom Kyte's book Effective Oracle by Design). The index is
> great, but I seem to need additional privileges compared to simpler
> indexes.
>
> create table T (IS_ACTIVE varchar2(1), F1 varchar2(1), F2 varchar(1));
>
> Table created.
>
> create unique index MY_INDEX1 on T ( F1 );
>
> Index created.
>
> create unique index MY_INDEX2
> on T ( case when IS_ACTIVE = 'N' then F1 end,
> case when IS_ACTIVE = 'N' then F2 end);
>
> case when IS_ACTIVE = 'N' then F2 end)
> *
> ERROR at line 3:
> ORA-01031: insufficient privileges
>
> Anyone know what the specific privilege could be?
> I have the following granted to this user:
>
> create any index
> create any procedure
> create any view
> create any snapshot
> create table
> unlimited tablespace
> alter any table
>
> plus the roles CONNECT and RESOURCE
>
> Thanks for any help.
> Dean

The usual and desirable route for questions like this is to go to http://tahiti.oracle.com, find the SQL-reference manual, search for CREATE INDEX in the Contents page, go to that entry and search for 'function-based index' on that page.
By doing so, I found the answer to your question in less than 1 minute.

I quote

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 functionbased  indexes in queries, the QUERY_REWRITE_ENABLED parameter must be set to TRUE, and the QUERY_REWRITE_INTEGRITY parameter must be set to TRUSTED. Could you please explain why you think it is faster to post to an Usenet forum instead of being only a *little* bit more industrious. You would have learned something on your own! Don't you like that? Or do you like to depend on this forum, or better still: to parasite on it, making it do *your* work?

--
Sybrand Bakker
Senior Oracle DBA