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