How to alter index change tablespace in Oracle 9i?

oracle9i

Solution

Yes, that will rebuild the `idx_city_state` index in the `sales_us` tablespace. You can write a bit of dynamic SQL to rebuild all the indexes

DECLARE
  l_sql_stmt VARCHAR2(1000);
BEGIN
  FOR i IN (SELECT owner,
                   index_name
              FROM all_indexes
             WHERE tablespace_name = 'MYDB_DATA_SPACE')
  LOOP
    l_sql_stmt := 'ALTER INDEX ' || i.owner || '.' || i.index_name ||
                  '  REBUILD TABLESPACE MYDB_INDX_SPACE';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;

Problem

I am a novice to Oracle DBMS and I have a task to change an index table space in Oracle e.g. from `MYDB_DATA_SPACE` to `MYDB_INDX_SPACE`. I have tried searching on Google for possible solutions and found such SQL: ``` ALTER INDEX idx_city_state REBUILD TABLESPACE sales_us; ``` Can I please check if this serves the purpose?

Original source