RE: rebuild index from a stored procedure permissions problem

Hi Ian,
You're a god amoungst men :-)

The sys priv req'd is CREATE ANY TABLE.

Thanks,
Tony

"Ian Cary (C)" <Ian.Cary_at_ordnancesurvey.co.uk>
02/25/2005 03:10 PM

To
<Tony.Adolph_at_o2.com>, <oracle-l_at_freelists.org> cc

Subject
RE: rebuild index from a stored procedure permissions problem

The problem lies with your 'online' keyword as this causes Oracle to create an IOT "under the covers" to support the index rebuild. You can either remove it or alternatively grant the user running the procedure explicit "CREATE ANY TABLE" and "CREATE ANY INDEX" privleges.

Cheers,

Ian

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tony.Adolph_at_o2.com Sent: 25 February 2005 13:42
To: oracle-l_at_freelists.org
Subject: Fw: rebuild index from a stored procedure permissions problem

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

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


This email and any attachments have been virus checked upon receipt at 
Ordnance Survey and are free of all known viruses.



This email is only intended for the person to whom it is addressed and may 
contain confidential information. If you have received this email in 
error, please notify the sender and delete this email which must not be 
copied, distributed or disclosed to any other person.
Unless stated otherwise, the contents of this email are personal to the 
writer and do not represent the official view of Ordnance Survey. Nor can 
any contract be formed on Ordnance Survey's behalf via email. We reserve 
the right to monitor emails and attachments without prior notice.

Thank you for your cooperation.

Ordnance Survey
Romsey Road
Southampton SO16 4GU
Tel: 023 8079 2000
http://www.ordnancesurvey.co.uk




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