Convert from Dictionary Managed Tablespace to Locally Managed Tablespace:

Convert from Dictionary Managed Tablespace to Locally Managed Tablespace:

SHAIKDB>select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME            EXTENT_MAN

------------------------------ ----------

SYSTEM                  DICTIONARY

SYSAUX                  LOCAL

UNDOTBS1                  LOCAL

TEMP1                  LOCAL

USERS                  LOCAL

Before migrating the SYSTEM tablespace, the following conditions must be met. These conditions are enforced by the TABLESPACE_MIGRATE_TO_LOCAL procedure, except for the cold backup.

• The database must have a default temporary tablespace that is not SYSTEM.
• Dictionary-managed tablespaces cannot have any rollback segments.
• A locally managed tablespace must have at least one online rollback segment. If you are using automatic undo management, then an undo tablespace must be online.
• All tablespaces—except the tablespace containing the rollback segment or the undo tablespace—must be read-only.
• You must have a cold backup of the database.
• The system must be in restricted mode.

SHAIKDB>shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SHAIKDB>startup restrict

ORACLE instance started.

Total System Global Area  471830528 bytes

Fixed Size           2214456 bytes

Variable Size         301991368 bytes

Database Buffers      163577856 bytes

Redo Buffers           4046848 bytes

Database mounted.

Database opened.

SHAIKDB>alter tablespace sysaux offline;

Tablespace altered.

SHAIKDB>exec dbms_space_admin.tablespace_migrate_to_local('SYSTEM');

PL/SQL procedure successfully completed.

SHAIKDB>select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME            EXTENT_MAN

------------------------------ ----------

SYSTEM                  LOCAL

SYSAUX                  LOCAL

UNDOTBS1                         LOCAL

TEMP1                  LOCAL

USERS                  LOCAL

SHAIKDB>

SHAIKDB>alter tablespace sysaux online;

Tablespace altered.

SHAIKDB>alter tablespace users read write;

Tablespace altered.

SHAIKDB>select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE