How to alter index change tablespace in Oracle 9i?

Question

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?

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;

Comments

[OCB]

Just another question, because i have no prior experience in altering index table spaces, just wondering how long it may take for an index occupying 2-3GB of table space to finish executing?

[Justin Cave]

@ChinBoon - It has to completely build the index. It's got to scan the table, build the index, etc. It will probably take roughly as long as it would take to create the index in the first place on your hardware-- probably at least a few minutes.