Re: Package Object Privilege Question

Aaron wrote:

> I had a package which had the code:
> FOR clm_curr_index IN (SELECT * FROM ALL_IND_PARTITIONS WHERE STATUS
> = 'UNUSABLE')
> LOOP
> EXECUTE IMMEDIATE 'ALTER INDEX '||clm_curr_index.index_name||'
> REBUILD PARTITION '||clm_curr_index.partition_name||' NOLOGGING
> PARALLEL';
> END LOOP;
>
> Which was getting 01555's, so I switched it to:
> TYPE TPartTable IS TABLE OF ALL_IND_PARTITIONS%ROWTYPE;
> t_PartTable TPartTable;
>
> SELECT *
> BULK COLLECT INTO t_PartTable
> FROM ALL_IND_PARTITIONS
> WHERE STATUS = 'UNUSABLE';
>
> FOR x IN t_PartTable.First..t_PartTable.Last
> LOOP
> EXECUTE IMMEDIATE 'ALTER INDEX '||t_PartTable(x).index_name||
> ' REBUILD PARTITION
> '||t_PartTable(x).partition_name||' NOLOGGING PARALLEL ONLINE';
> END LOOP;
>
> But now I am getting ORA-01031 (insufficient priv) on the ALTER INDEX
> statement. I am reluctant to add specific Object priv's to all the
> indexes and I am wondering why I need them now and didn't in the prior
> code. The only difference is one uses variables via cursor and the
> other via table.
>
> Any advice would be appreciated.. Thanks.

You don't solve ORA-01555 by rewriting your code. You learn what causes a 1555 and fix the problem.

If 9i or above switch to Undo rather than rollback segments.

If 8i or before increase the number and/or size of rollback segments to handle the transaction.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)