Manage Learn to apply best practices and optimize your operations.

Changing managed tablespaces when upgrading

Oracle expert Brian Peasland explains it is not necessary to change your tablespaces to Locally Managed at any stage in the upgrade process.

I am an Oracle DBA going to perform an upgrade on Sun OS 5.9, from Oracle 9i version 9.2.0 and 9.0.4 to Oracle 10g. I am confused whether I should change all the Dictionary Managed tablespaces to Locally Managed before upgrading to 10g, including system, or should I change all the tablespaces, except system, and change the system later on, after the upgrade?
You do not have to change your tablespaces to Locally Managed at any stage in the upgrade process. This change is completely optional. And you can perform the change to Locally Managed Tablespoaces (LMTs) at any time. It is entirely up to you.

When I have a tablespace that is Dictionary Managed, I do not convert it to a LMT. Instead, I create a new LMT tablespace and move segments into this new tablespace. Tables are moved with the ALTER TABLE MOVE command and indexes are moved with the ALTER INDEX REBUILD command. Once the old DMT tablespace is empty, it can be dropped.

Dig Deeper on Oracle database administration

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.