Solution

You can use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a tablespace
from dictionary managed to locally managed. Perform. the following steps to migrate your
dictionary-managed tablespaces to locally managed. The tablespace must be kept online and
read/write during migration. Note that temporary tablespaces cannot be migrated. You must migrate
all non-SYSTEM tablespaces prior to migrating the SYSTEM tablespace if you intend for those
tablespaces to be used in READ WRITE mode.

1) To Migrate a Non-SYSTEM Tablespace

1. Invoke SQL*Plus.

2. Query DBA_TABLESPACES to determine the type of extent management used in each tablespace:

SELECT tablespace_name, extent_management
FROM dba_tablespaces;

3. Query DBA_TABLESPACES to verify that the tablespace you want to migrate is online and in READ
WRITE mode:

SELECT tablespace_name, status
FROM dba_tablespaces;

4. Execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure and specify the name of
the tablespace you want to migrate:

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('');

5. Query DBA_TABLESPACES to verify your change:

SELECT tablespace_name, status, extent_management
FROM dba_tablespaces;

2) To Migrate the SYSTEM Tablespace :

As of Oracle9i Release 2, you can define all tablespaces, including the SYSTEM tablespace, as
locally managed tablespaces.

You can use the TABLESPACE_MIGRATE_TO_LOCAL procedure of the DBMS_SPACE_ADMIN package to migrate
the SYSTEM tablespace from dictionary-managed to a locally managed tablespace.

The following conditions must be met before the migration:
The database must have a default temporary tablespace that is not SYSTEM
If you are using rollback segments:
There are no rollback segments in dictionary-managed tablespaces
There is at least one online rollback segment in a locally managed tablespace
If you are using automatic undo management, an undo tablespace must be online
All tablespaces, other than the tablespace containing rollback segments or undo segments, must
be in read-only mode
The database must be in RESTRICTED mode

All of these conditions are enforced by the TABLESPACE_MIGRATE_TO_LOCAL procedure. You will
receive an error message if any of the conditions are not met.

Note: After the SYSTEM tablespace is migrated to locally managed, any dictionary-managed
tablespaces in the database cannot be made READ WRITE. If you want to be able to use the
dictionary-managed tablespaces in READ WRITE mode, you should migrate these tablespaces to locally
managed before migrating the SYSTEM tablespace.

You can migrate your SYSTEM tablespace from dictionary-managed to locally managed by performing
the following steps:
          1. Use Recovery Manager or operating system commands to make a whole consistent (complete or
full) backup of the database before you begin the migration.

        2. Invoke SQL*Plus. Connect as a user with SYSDBA privileges.

        3. Verify that you have a temporary tablespace other than SYSTEM by querying DBA_TABLESPACES as follows:

SELECT tablespace_name
FROM dba_tablespaces
WHERE contents = 'TEMPORARY';

             4. If you are using rollback segments, verify that no rollback segments are in
dictionary-managed tablespaces by querying DBA_SEGMENTS and DBA_TABLESPACES:

SELECT s.segment_name, s.tablespace_name, t.extent_management
FROM dba_segments s, dba_tablespaces t
WHERE s.tablespace_name = t.tablespace_name
AND s.segment_type = 'ROLLBACK';

NOTE:
---------
   As per Note 135090.1 "Rollback Segments versus UNDO Segments" point 3
says that "Only undo segments of the active UNDO tablespace and the SYSTEM rollback segment are
kept ONLINE. All other rollback segments and undo segments of other UNDO tablespaces are OFFLINE. ".

 That means that this Default System Rollback Segment, cannot be dropped and you can proceed the system
tablespace conversion from Dictionary Managed into Locally Managed without having to drop this unique System Rollback Segment, and that explains the test you did in a test environment by keeping this RBS and proceeding successfully in the conversion without issues.


        5. If you are using rollback segments, verify that there is at least one online rollback segment
in a locally managed tablespace by querying DBA_ROLLBACK_SEGS:

SELECT segment_name, status
FROM dba_rollback_segs
WHERE tablespace_name = '';

6. If you are using automatic undo management, verify that the undo tablespace is online by
querying DBA_TABLESPACES as follows:

SELECT tablespace_name, status
FROM dba_tablespaces
WHERE contents = 'UNDO';

7. Verify that all tablespaces other than the ones containing rollback segments or undo segments
and your temporary tablespace are READ ONLY by querying DBA_TABLESPACES as follows:

SELECT tablespace_name, status
FROM dba_tablespaces;

8. If any tablespace other than the tablespace containing undo or rollback segments or the
temporary tablespace is not in READ ONLY mode, issue the following command to place it in READ
ONLY mode:

ALTER TABLESPACE READ ONLY;

Please note that it doesn't work for SYSAUX, so we have to put
sysaux offline.

9. Verify that the database is in RESTRICTED mode by querying V$INSTANCE:

SELECT logins
FROM v$instance;

10. If the database is not in RESTRICTED mode, issue the following command to put it in
RESTRICTED mode:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

11. Execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure specifying the SYSTEM
tablespace:

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

12. Verify your change by querying DBA_TABLESPACES:

SELECT extent_management
FROM dba_tablespaces
WHERE tablespace_name = 'SYSTEM';

13. Issue the following command to disable RESTRICTED mode:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

14. Issue the following command for each tablespace you placed in READ ONLY mode to return it to
READ WRITE:

ALTER TABLESPACE READ WRITE;


3) To Migrate a Temporary Tablespace :

You cannot use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a temporary
tablespace that is dictionary-managed to a locally managed tablespace. You must drop the
dictionary-managed temporary tablespace and re-create it as locally managed.

Because space management is much simpler and more efficient in locally managed tablespaces, they
are ideally suited for temporary tablespaces. Locally managed temporary tablespaces use tempfiles,
that do not modify data outside of the temporary tablespace or generate any redo for temporary
tablespace data.


1. Invoke SQL*Plus and connect as a user with the CREATE TABLESPACE privilege.

2. Query the DBA_TABLESPACES view to determine the name of your dictionary-managed temporary
tablespace:

SELECT tablespace_name, extent_management
FROM dba_tablespaces
WHERE contents = 'TEMPORARY';

3. Use the DROP TABLESPACE command to drop your dictionary-managed temporary tablespace. You can
include the INCLUDING CONTENTS AND DATAFILES clause to remove the file from the operating system.
Alternatively, you can remove the data file with operating system commands.

DROP TABLESPACE

4. Use the CREATE TEMPORARY TABLESPACE command to create a locally managed temporary tablespace
as shown below:

CREATE TEMPORARY TABLESPACE
TEMPFILE '' SIZE 2M
AUTOEXTEND ON;

5. Query DBA_TABLESPACES view to verify the creation of your locally managed temporary tablespace:

SELECT tablespace_name, extent_management
FROM dba_tablespaces
WHERE contents = 'TEMPORARY';

6. Query V$TEMPFILE or DBA_TEMP_FILES to determine the names of the tempfiles:

SELECT ts#, name
FROM v$tempfile;

SELECT tablespace_name, file_name
FROM dba_temp_files;

Please Review the following notes for more details :

Note 175434.1 - Create/Upgrade/Migrate a Database to Have a Locally Managed SYSTEM Tablespace
Note 238017.1 - Training Available - Lesson: Migrating to Locally Managed Tablespaces

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/tspaces.htm#25019
Migrating the SYSTEM Tablespace to a Locally Managed Tablespace

References

NOTE:109630.1 - How and Where Bitmaps are Allocated for Locally Managed Tablespaces
NOTE:175434.1 - Create/Upgrade/Migrate a Database to Have a Locally Managed SYSTEM Tablespace
NOTE:238017.1 - Training Available - Lesson: Migrating to Locally Managed Tablespaces