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

ps Make that Oracle 9.2i on Windows.

-Dean

On Jun 18, 4:01 pm, dean <deanbrow...@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 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

On Jun 18, 10:45 am, sybrandb <sybra...@gmail.com> wrote:
> On Jun 18, 4:01 pm, dean <deanbrow...@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.