Privileges for creating indexes depends on type of index?

From: dean <deanbrown3d_at_yahoo.com>
Date: Mon, 18 Jun 2007 14:01:20 -0000
Message-ID: <1182175280.301763.139090@w5g2000hsg.googlegroups.com>

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 Received on Mon Jun 18 2007 - 09:01:20 CDT