Locally Managed SYSTEM Tablespace

Locally Managed SYSTEM Tablespace

1. The SYSTEM tablespace can be locally managed only if COMPATIBLE is set to 9.2.0 or higher.

2. The SYSTEM tablespace can be migrated from dictionary managed format to locally managed format using the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure.

3. The database has a default temporary tablespace which is not SYSTEM.
SQL> alter database default temporary tablespace TEMPX;

4. Start the system is in restricted mode.
$dbbin/stop_db.sh –d
SQL> conn / as sysdba
SQL> startup restrict

5. Migrate all the Dictionary managed tablespaces to Locally managed tablespaces using DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure.
SQL> select 'execute Sys.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('''||
tablespace_name||'''); ' from dba_tablespaces where tablespace_name not in ('SYSTEM') and EXTENT_MANAGEMENT<>'LOCAL';

6. There are not any rollback segments in dictionary managed tablespaces(ignore system rollback segment).
SQL> @$dbsql/rollbackinfo.sql
7. There is at least one online rollback segment in a locally managed tablespace, or an undo tablespace (if using automatic undo management mode) should be online.
SQL> @$dbsql/rollbackinfo.sql

8. All tablespaces other than the tablespace containing the undo space (undo tablespace or the tablespace containing the rollback segment) and the default temporary tablespace are in read-only mode.
SQL> select 'ALTER TABLESPACE '||TABLESPACE_NAME||' READ ONLY; '
from dba_tablespaces where tablespace_name not in ('SYSTEM', 'APPS_UNDOTS1', 'TEMPX');

9. Check the status by running the following command:
SQL> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES
WHERE STATUS<>'READ ONLY';

10. Migrate SYSTEM tablespace as locally managed
SQL> conn / as sysdba
SQL> execute sys.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

11. Once the SYSTEM tablespace has been migrrated to locally managed format, you will not be able to downgrade back to a previous release.

12. The following query determines whether the SYSTEM tablespace is locally managed:
SQL> SELECT ts# FROM ts$ WHERE ts# = 0 AND bitmapped <> 0;
If 0 rows is returned, then the SYSTEM tablespace is dictionary managed. Otherwise, the SYSTEM tablespace is locally managed.

13. Change the tablespaces back to READ WRITE, by executing the following:
SQL> select 'ALTER TABLESPACE '||TABLESPACE_NAME||' READ WRITE; '
from dba_tablespaces where tablespace_name not in ('SYSTEM', 'APPS_UNDOTS1', 'TEMPX');

14. Check for any tablespaces that are not ONLINE
SQL> select tablespace_name, status from dba_tablespaces
where status<>'ONLINE';

15. Bring the database out of Restrict mode using the following command:
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Warning:
Starting with release 9.2, dictionary managed tablespaces are deprecated. Once the SYSTEM tablespace has been migrated from dictionary managed format to locally managed format, existing dictionary managed tablespaces are read-only. That is, they cannot be made read-write once the SYSTEM tablespace is locally managed. Once the SYSTEM tablespace is locally managed (either due to a new installation of SYSTEM tablespace migration), new dictionary managed tablespaces cannot be created.