RE: rebuild index from a stored procedure permissions problem
- From: Tony.Adolph@xxxxxx
- To: "Ian Cary (C)" <Ian.Cary@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 25 Feb 2005 16:19:41 +0100
Hi Ian,
You're a god amoungst men :-)
The sys priv req'd is CREATE ANY TABLE.
Thanks,
Tony
"Ian Cary (C)" <Ian.Cary@xxxxxxxxxxxxxxxxxxxx>
02/25/2005 03:10 PM
To
<Tony.Adolph@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Tony.Adolph@xxxxxx
Sent: 25 February 2005 13:42
To: oracle-l@xxxxxxxxxxxxx
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
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
