Q
Manage Learn to apply best practices and optimize your operations.

How to change to locally managed tablespaces

We have dictionary-managed tablespaces and plan to make them locally managed. How do we do that?

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;

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close