My company's database size is 3.2 terabytes. And we have dictionary-managed tablespaces. Now we plan to make them locally managed. Can you please give me the detailed steps for that? Also my indexes have rows something near to 30 to 35 million. So, what are the steps to rebuild them in a fast way? We are using Oracle 9i. Please give me detailed explanations on both topics. We get 24 hours downtime, three times a month. We're using Linux. Thanks.
When I migrate from dictionary-managed tablespaces to locally managed ones, I always precreate the new tablespace as a LMT and then copy the objects into that new tablespace. You can copy indexes as follows:
ALTER INDEX owner.index_name REBUILD TABLESPACE new_tablespace;
Tables can be moved as follows:
ALTER TABLE owner.table_name MOVE TABLESPACE new_tablespace;