是否有一种方法可以通过存储过程/ plsql重建每个模式中的每个索引？

Question

无法显示一个存储过程，该存储过程将在线重建数据库中每个架构中的每个索引。我只是对这件事不太清楚。

create or replace procedure rebuild_indexes(
    p_owner in varchar2,
    p_table_name in varchar2
) as
begin
    for indexes_to_rebuild in
    (
        select index_name
        from all_indexes
        where owner = p_owner
        and table_name = p_table_name
    ) loop
        execute immediate 'alter index '||p_owner||'.'
            ||indexes_to_rebuild.index_name||' rebuild online';
    end loop;
end;
/

Answer 1

您可以通过稍微修改参数和对ALL_INDEXES进行查询来尝试这样的过程

DECLARE
    PROCEDURE rebuild_indexes (p_owner        IN VARCHAR2 DEFAULT NULL,
                               p_table_name   IN VARCHAR2 DEFAULT NULL)
    AS
    BEGIN
        FOR indexes_to_rebuild
            IN (SELECT owner, index_name
                  FROM all_indexes
                 WHERE owner = NVL (p_owner, owner) AND table_name = NVL (p_table_name, table_name))
        LOOP
            BEGIN
                EXECUTE IMMEDIATE   'alter index '
                                 || indexes_to_rebuild.owner
                                 || '.'
                                 || indexes_to_rebuild.index_name
                                 || ' rebuild online';
            EXCEPTION
                WHEN OTHERS
                THEN
                    DBMS_OUTPUT.put_line (
                           'Failed to rebuild index '
                        || indexes_to_rebuild.owner
                        || '.'
                        || indexes_to_rebuild.index_name
                        || ': '
                        || SQLERRM);
            END;
        END LOOP;
    END;
BEGIN
    --Rebuild all indexes on a table
    rebuild_indexes (p_owner => 'APEX_190200', p_table_name => 'WWV_FLOWS');
    --Rebuild all indexes in a schema
    rebuild_indexes (p_owner => 'APEX_190200', p_table_name => NULL);
    --Rebuild all indexes
    rebuild_indexes (p_owner => NULL, p_table_name => NULL);
END;
/