Summary: alter index .. (storage ..) fails with ORA-00054. Workaround possible?

No, it is not possible to alter the storage clause of an index when there are uncommitted write-transactions on the index. Thanks goes to Mogens Nørgaard of Oracle/Denmark for his support regarding this problem. He also verified that the same is true for Oracle 8.1.5.

Regards, Hans Henrik Krohn

Hans Henrik Krohn wrote:

> An application, performing a big update-batchjob that is 99,9 %
> completed, fails with the message
> ORA-01632: max # extents ... reached in index ...
> and the application gives the opportunity to retry the sql-statement
> that failed.
> But attempts to increase maxextent using
> alter index ... storage (maxextents 9999)
> fails with error message
> ORA-00054: resource busy and acquire with NOWAIT specified
>
> The urgent question is of course whether there is a workaround that
> alters the storage clause for the index even though it is locked? It
> anybody has any ideas, I will be profoundly gratefull, please email
> directly to hhk_at_eud.dk.
>
> Database in question is Oracle 7.3.4.2 on True64 Unix.
>
> The non-urgent question is of course why it <_is_> possible to alter the
> storage clause for a table thas is being updated upon (I know this,
> having done it often enough), but not possible to do the same for an
> index?
>
> Regards,
>
> Hans Henrik Krohn
> EU-direktoratet
> Denmark
> hhk_at_eud.dk
Received on Sat Dec 18 1999 - 07:51:25 CST