按用户重建索引

--赋用户表空间分配权限

ALTER USER YBSH_BASE QUOTA UNLIMITED ON TS_YBSH_BASE ;

 

--查询已有索引

select * from user_indexes where index_name='INDX_REG_SI_ITEM3'

 

--创建重建索引

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;

 

--执行重建索引

begin

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

end;