Cannot disable index during PL/SQL procedure
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开发者_如何学JAVA 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.
Edit: Here is the procedure in its entirety:
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;
If this is really your code and not a pseudo-code rewrite, remove the ;
at the end of your statement in the stmt
variable (otherwise you will run into ORA-00911: invalid character
during execution)
Now if your process works manually, you should be able to make it work with execute immediate
in a procedure. Make sure that this is not a role issue (see this article by Tom Kyte) by issuing SET ROLE NONE
before executing the commands manually.
Have you tried to do a DROP INDEX before and then recreate it after?
精彩评论