On Jun 18, 10:45 am, sybrandb <sybra..._at_gmail.com> wrote:
> 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 tohttp://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 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.
>
> 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- Hide quoted text -
>
> - Show quoted text -
Thanks for the reply! I didn't know it was a function based index,
sorry.
Received on Mon Jun 18 2007 - 13:29:05 CDT
