oracle 无法在 PL/SQL 过程中禁用索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2814159/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot disable index during PL/SQL procedure
提问by nw.
I've written a PL/SQL procedure that would benefit if indexes were first disabled, then rebuilt upon completion. An existing threadsuggests this approach:
我已经编写了一个 PL/SQL 过程，如果索引首先被禁用，然后在完成后重建，它将会受益。一个现有的线程建议这种方法：
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 indexstatement:
但是，我在第一条alter index语句中收到以下错误：
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.
这是来自 3rd 方供应商的自定义索引类型，它会在大量更新/插入/删除操作期间导致性能长期下降。
Any suggestions on how to work around this error? By the way, this error only occurs within a PL/SQL block.
有关如何解决此错误的任何建议？顺便说一下，这个错误只发生在 PL/SQL 块中。
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;
采纳答案by Vincent Malgrat
If this is really your code and not a pseudo-code rewrite, remove the ;at the end of your statement in the stmtvariable (otherwise you will run into ORA-00911: invalid characterduring execution)
如果这真的是你的代码，而不是一个伪代码重写，删除;你的陈述在结束stmt变量（否则你会遇到ORA-00911: invalid character执行过程中）
Now if your process works manually, you should be able to make it work with execute immediatein a procedure. Make sure that this is not a role issue (see this article by Tom Kyte) by issuing SET ROLE NONEbefore executing the commands manually.
现在，如果您的流程手动工作，您应该能够execute immediate在一个过程中使用它。通过在手动执行命令之前发出来确保这不是角色问题（请参阅Tom Kyte 的这篇文章）SET ROLE NONE。
回答by Lost in Alabama
Have you tried to do a DROP INDEX before and then recreate it after?
你有没有试过之前做一个 DROP INDEX 然后重新创建它？
