Switch index tablespace

I

It is a common thing to store indexes in different tablespace. In the past it was mainly for performance reasons, because you put this tablespace to faster drives. But there are more reasons and you can find them elsewhere.

Overview of indexes and tablespaces.

SELECT
    i.table_name,
    t.tablespace_name   AS table_tablespace,
    i.index_name,
    i.index_type,
    i.tablespace_name   AS index_tablespace,
    i.status
FROM user_indexes i
JOIN user_tables t
    ON t.table_name     = i.table_name
WHERE i.index_type      != 'LOB'
ORDER BY i.table_name, i.index_name;

Move indexes to new tablespace:

DECLARE
    in_target_tablespace    CONSTANT VARCHAR2(30) := 'INDEXES';
BEGIN
    FOR c IN (
        SELECT i.table_name, i.index_name
        FROM user_indexes i
        WHERE i.index_type          LIKE '%NORMAL%'
            AND i.tablespace_name   != in_target_tablespace
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(c.table_name, 30) || ' -> ' || c.index_name);
        EXECUTE IMMEDIATE
            'ALTER INDEX ' || c.index_name ||
            ' REBUILD TABLESPACE ' || in_target_tablespace;
    END LOOP;
END;
/