Oracle按用户批量重建索引

按用户批量重建索引：

按用户将此用户下面非临时表上面的索引全部重建，此过程建议在SYS用户下面执行：

Sql代码

1. CREATE OR REPLACE PROCEDURE BATCH_REBUILD_INDEX(USER_NAME IN VARCHAR2) IS
2. S_SQL VARCHAR2(500);
3. ACCOUNT NUMBER := 0;
4. BEGIN
6. FOR LINE2 IN (SELECT T.OWNER, T.INDEX_NAME
7. FROM ALL_INDEXES T
8. WHERE T.OWNER = UPPER(USER_NAME)
9. AND T.TABLE_TYPE = 'TABLE'
10. AND T.TEMPORARY = 'N'
12. AND T.INDEX_TYPE = 'NORMAL') LOOP
13. S_SQL := 'alter index ' || LINE2.OWNER || '.' || LINE2.INDEX_NAME ||
14. ' rebuild';
15. ACCOUNT := ACCOUNT + 1;
16. EXECUTE IMMEDIATE S_SQL;
17. END LOOP;
18. DBMS_OUTPUT.PUT_LINE(ACCOUNT);
19. EXCEPTION
20. WHEN OTHERS THEN
21. DBMS_OUTPUT.PUT_LINE(SQLERRM);
22. END BATCH_REBUILD_INDEX;

CREATE OR REPLACE PROCEDURE BATCH_REBUILD_INDEX(USER_NAME IN VARCHAR2) IS
  S_SQL   VARCHAR2(500);
  ACCOUNT NUMBER := 0;
BEGIN

  FOR LINE2 IN (SELECT T.OWNER, T.INDEX_NAME
                  FROM ALL_INDEXES T
                 WHERE T.OWNER = UPPER(USER_NAME)
                   AND T.TABLE_TYPE = 'TABLE'
                   AND T.TEMPORARY = 'N'

                   AND T.INDEX_TYPE = 'NORMAL') LOOP
    S_SQL   := 'alter index ' || LINE2.OWNER || '.' || LINE2.INDEX_NAME ||
               ' rebuild';
    ACCOUNT := ACCOUNT + 1;
    EXECUTE IMMEDIATE S_SQL;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(ACCOUNT);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END BATCH_REBUILD_INDEX;

过程在SYS用户下面创建完成后，用下面的代码调整创建好的存储过程：

Sql代码

1. begin
3. -- Call the procedure
5. batch_rebuild_index(user_name => 'hs_user'); --输入用户名
7. end;

begin

  -- Call the procedure

  batch_rebuild_index(user_name => 'hs_user'); --输入用户名

end;