I need the syntax for moving an indexes from one tablespace to another. I am taking on Oracle DBA responsibilities and i am discovering massive problems. The version of Oracle is 22.214.171.124
alter index xyz_pk rebuild tablespace new_ts;
Will the above syntax is OK , if the space is not enough on the disk. Or is there any other way to move the indexes without rebuilding them, because of lack of space.
You can DROP and re-CREATE the index.
I recently move all my INDEXES into a new tablespace. I used the following procedure:
1. Connect as the owner of the Indexes that you want to move
2. Set Lines n (depending on the number of index)
3. Spool filename.sql
4. SELECT 'ALTER INDEX ' || index_name || ' REBUILD TABLESPCE new_tablespace;' FROM user_indexes;
I hope this helps =)
Oracle Certified DBA - 8i
Keep in mind, if you don't specify the storage paramaters, the index will keep it's origional settings. Even ignoring the Tablespace Defaults.
OCP 8i, 9i DBA
Click Here to Expand Forum to Full Width