Convert tablespace from dictionary managed to Local extent management

[oracle@collabn1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 5 11:51:17 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SHAIKDB>select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME            EXTENT_MAN

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

SYSTEM                  DICTIONARY

SYSAUX                  LOCAL

UNDOTBS1              LOCAL

TEMP1                  LOCAL

USERS                  LOCAL

SHAIKDB>alter tablespace users read only;

Tablespace altered.

SHAIKDB>alter tablespace sysaux offline;

Tablespace altered.

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

BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;

*

ERROR at line 1:

ORA-10643: Database should be mounted in restricted mode and Exclusive mode

ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227

ORA-06512: at line 1

SHAIKDB>shut immediate;

Database closed.

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>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.

Remember you cannot convert a tablespace from LOCAL to DICTIONARY unless the system tablespace is already in DICTIONARY managed state. ELSE you will get the below error message.


SHAIKDB>exec dbms_space_admin.tablespace_migrate_from_local('PERM');
BEGIN dbms_space_admin.tablespace_migrate_from_local('PERM'); END;

*
ERROR at line 1:
ORA-10616: Operation not allowed on this tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1