oracle - 重建特定Oracle表的所有普通索引

Question

我们有一个迁移脚本，该脚本将LONG列转换为LOB，并且如Oracle migration guide中所述，现在需要重建表的索引。

假设表名是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;

但是，它失败并显示以下语法错误:

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]

即使这似乎与此处指定的语法匹配: Database PL/SQL Language Reference
ALTER在循环中不是有效命令吗？

编辑:在lad2025的建议下，尝试像这样使用 EXECUTE IMMEDIATE:

5: LOOP
6:     execute immediate 'alter index ' || index_entry.index_name || ' rebuild';
7: END LOOP;

我收到:

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)

编辑2: EXECUTE IMMEDIATE正常工作。文件结束问题与Liquibase执行我的脚本有关，而我忘记了使用以下命令定义 <sql>块:

<sql dbms="oracle" splitStatements="false">
                                    ^ defaults to true

至关重要的是，默认情况下，Liquibase会在分号处拆分语句，因此需要将其关闭。

Answer 1

您不能在PL/SQL块中使用DDL语句。使用Dynamic-SQL:

BEGIN
    ...
    EXECUTE IMMEDIATE 'ALTER INDEX ' || index_entry.INDEX_NAME || ' REBUILD';
END

编辑:

尝试:

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;
/

SqlFiddleDemo