Migrating the SYSTEM Tablespace from dictionary to local

Migrating the SYSTEM Tablespace Back to List of Topics 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 dictionarymanaged 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 dictionarymanaged 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 dictionarymanaged 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'; 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; 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;