How to change to locally managed tablespaces

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.

    Requires Free Membership to View

    Login

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;

This was first published in May 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top