重建特定Oracle表的所有常规索引

我们有一个将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会以分号分割语句,这需要关闭.

解决方法

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

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!