Unable to disable index during PL / SQL procedure

Question

I wrote a PL / SQL procedure that would be useful if indexes were first disabled and then restored after completion. The existing thread offers this approach:

alter session set skip_unusable_indexes = true; alter index your_index unusable; 

[make import]

 alter index your_index rebuild; 

However, the following error appears in the first alter index expression:

 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 (eg MOVE PARTITION) with some other operation (eg 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 as follows:

 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 third-party vendor, and this leads to chronic performance degradation during high-volume update / insert / delete operations.

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

Edit: Here is the whole procedure:

 procedure disable_indexes ( tbl_name in varchar2 ) as stmt varchar2(200); cursor curs(v_tbl_name in varchar2) is select 'alter index ' || index_name || ' unusable;' as ddl_stmt from user_indexes where upper(table_owner) = upper(user) and upper(table_name) = upper(v_tbl_name) and ityp_name in ('CTXCAT', 'ST_SPATIAL_INDEX'); begin for r_curs in curs(tbl_name) loop dbms_output.put_line(r_curs.ddl_stmt); execute immediate r_curs.ddl_stmt; end loop; end; 

Answer 1

If this is really your code, not a rewrite of the pseudocode, delete ; at the end of your statement in the stmt variable (otherwise you will work in ORA-00911: invalid character at run time)

Now, if your process runs manually, you can make it work with execute immediate in the procedure. Make sure this is not a problem for the role (see this article in Tom Kyte's article ) by issuing SET ROLE NONE before executing the commands manually.

Answer 2

Have you tried to make DROP INDEX before and then recreate it after?