Move Whole Index From one Tablespace to Another Tablespace

CREATE OR REPLACE PROCEDURE SP_INDEX_MOVE

(P_USER_NAME VARCHAR2,

 P_NEW_TABLESPACE VARCHAR2

)

 IS

 V_INDEX_NAME VARCHAR2(100);

 V_IDX_SCRIPTS VARCHAR2(3000);

 BEGIN

    FOR IND IN (

        SELECT OWNER, INDEX_NAME, TABLESPACE_NAME

        FROM DBA_INDEXES

        WHERE OWNER=P_USER_NAME

        AND TABLESPACE_NAME<>P_NEW_TABLESPACE

        AND TABLE_NAME NOT LIKE 'SYS%'

       AND INDEX_TYPE<>'LOB')

    LOOP

       

    V_IDX_SCRIPTS:='ALTER INDEX '||IND.OWNER||'.'||IND.INDEX_NAME||' REBUILD TABLESPACE '||P_NEW_TABLESPACE;

    

        BEGIN

          EXECUTE IMMEDIATE V_IDX_SCRIPTS;

          

         EXCEPTION

                WHEN OTHERS THEN 

                 DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILD INDEX' || IND.INDEX_NAME ||' : '|| SQLERRM);

        END;

    

    END LOOP;    

 END;

============= Execution ==========

EXEC SP_INDEX_MOVE(USER,'TABLESPACE_NAME');