Cannot disable index during PL/SQL procedure

Posted by nw on Stack Overflow See other posts from Stack Overflow or by nw
Published on 2010-05-11T20:21:54Z Indexed on 2010/05/11 20:54 UTC
Read the original article Hit count: 164

Filed under:
|
|

I've written a PL/SQL procedure that would benefit if indexes were first disabled, then rebuilt upon completion. An existing thread suggests this approach:

alter session set skip_unusable_indexes = true;

alter index your_index unusable;

[do import]

alter index your_index rebuild;

However, I get the following error on the first alter index statement:

SQL Error: ORA-14048: a partition maintenance operation may not be combined with other operations
ORA-06512: [...]
14048. 00000 -  "a partition maintenance operation may not be combined with other operations"
*Cause:    ALTER TABLE or ALTER INDEX statement attempted to combine
           a partition maintenance operation (e.g. MOVE PARTITION) with some
           other operation (e.g. ADD PARTITION or PCTFREE which is illegal
*Action:   Ensure that a partition maintenance operation is the sole
           operation specified in ALTER TABLE or ALTER INDEX statement;
           operations other than those dealing with partitions,
           default attributes of partitioned tables/indices or
           specifying that a table be renamed (ALTER TABLE RENAME) may be
           combined at will

The problem index is defined so:

CREATE INDEX A11_IX1 ON STREETS ("SHAPE")
  INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS
  ('ST_GRIDS=890,8010,72090 ST_SRID=2');

This is a custom index type from a 3rd-party vendor, and it causes chronic performance degradation during high-volume update/insert/delete operations.

Any suggestions on how to work around this error? By the way, this error only occurs within a PL/SQL block.

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about plsql