为特定的 Oracle 表重建所有正常索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33441774/
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
Rebuilding all normal indexes for a specific Oracle table
提问by Craig Otis
We have a migration script that converts a LONG column to a LOB, and as mentioned in the Oracle migration guide, the index for the table now needs rebuilding.
我们有一个将 LONG 列转换为 LOB 的迁移脚本，并且如Oracle 迁移指南中所述，现在需要重建表的索引。
Assuming the table name is MY_TABLE, I've been attempting to run this script:
假设表名是MY_TABLE，我一直在尝试运行这个脚本：
BEGIN
FOR index_entry IN (
select INDEX_NAME from user_indexes where table_name='MY_TABLE' and index_type='NORMAL'
)
LOOP
ALTER INDEX index_entry.index_name REBUILD;
END LOOP;
END;
However, it fails with the following syntax error:
但是，它失败并出现以下语法错误：
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
[Failed SQL: BEGIN
FOR index_entry IN (
select INDEX_NAME from user_indexes where table_name='MY_TABLE' and index_type='NORMAL'
)
LOOP
ALTER INDEX index_entry.index_name REBUILD]
Even though this seemsto match the syntax specified here: Database PL/SQL Language Reference
即使这似乎与此处指定的语法匹配：数据库 PL/SQL 语言参考
Is ALTERnot a valid command to use in a loop?
是ALTER不是有效的命令在循环使用？
Edit:At lad2025's suggestion, attempting to use EXECUTE IMMEDIATElike so:
编辑：在lad2025的建议下，尝试EXECUTE IMMEDIATE像这样使用：
5: LOOP
6: execute immediate 'alter index ' || index_entry.index_name || ' rebuild';
7: END LOOP;
I receive:
我收到：
ORA-06550: line 6, column 92:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || bulk member
submultiset
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
Edit 2:The EXECUTE IMMEDIATEworked properly. The end-of-file issue was related to Liquibase executing my script, and my forgetting to define my <sql>block with:
编辑2：在EXECUTE IMMEDIATE正常工作。文件结束问题与 Liquibase 执行我的脚本有关，并且我忘记定义我的<sql>块：
<sql dbms="oracle" splitStatements="false">
^ defaults to true
Critically, Liquibase by default splits statements at the semicolon, and this needed to be turned off.
重要的是，Liquibase 默认情况下在分号处拆分语句，这需要关闭。
回答by Lukasz Szozda
You cannot use DDL statements in PL/SQL block. Use Dynamic-SQL:
不能在 PL/SQL 块中使用 DDL 语句。使用动态 SQL：
BEGIN
...
EXECUTE IMMEDIATE 'ALTER INDEX ' || index_entry.INDEX_NAME || ' REBUILD';
END
EDIT:
编辑：
Try:
尝试：
DECLARE
BEGIN
FOR index_entry IN (select INDEX_NAME
from user_indexes
where table_name='MY_TABLE' and
index_type='NORMAL')
LOOP
dbms_output.put_line('ALTER INDEX ' || index_entry.INDEX_NAME || ' REBUILD');
EXECUTE IMMEDIATE 'ALTER INDEX ' || index_entry.INDEX_NAME || ' REBUILD';
END LOOP;
END;
/
