Fw: rebuild index from a stored procedure permissions problem

From: Tony.Adolph@xxxxxx
To: oracle-l@xxxxxxxxxxxxx
Date: Fri, 25 Feb 2005 14:41:57 +0100

Forgot again: Oracle 9.2.0.5 on Red Hat Enterprise Linux ES release 3

I have some partitioned tables on date.  Each month I plan to add a new 
partition and drop an old one.

I've written some procedures to do this and apart from a small hitch they 
all seem to work well... to my query:

The following commands run via execute immediate (by the stored procedure)

adding a new partition:
alter table AUDIT_TRAILS split partition P12 at 
(to_date('200512','YYYYMM')) into (partition P12, partition p13)

adding new partition P09 to AUDIT_TRAILS_ARC:
alter table AUDIT_TRAILS_ARC add partition P09 values less than (TO_DATE(' 
2005-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

swaping partition P09 to AUDIT_TRAILS_TEMPARC:
alter table AUDIT_TRAILS exchange partition P09 with table 
AUDIT_TRAILS_TEMPARC including indexes without validation

swaping partition P09 to AUDIT_TRAILS_ARC:
alter table AUDIT_TRAILS_ARC exchange partition P09 with table 
AUDIT_TRAILS_TEMPARC including indexes without validation

droping partition P09 from AUDIT_TRAILS:
alter table AUDIT_TRAILS drop partition P09

All works ok, but if there was data in the partition that was split then 
its indexes go UNUSABLE, so I run:
"alter index PK_AUDIT_TRAILS rebuild partition P12 online"  again from the 
sp, but this fails reporting that I don't have permissions.

If I cut and paste the line it works.  I guessed it might be a problem of 
getting a permission through a role, so I gave my user ALTER ANY INDEX 
system priv, but get the same error.

Any ideas?

Cheers
Tony

--
//www.freelists.org/webpage/oracle-l

Fw: rebuild index from a stored procedure permissions problem

Other related posts: