How to migrate from dictionary manged tablespace to local management talespace?
We have plan to migrate the below dictionary managed tablespaces to locally managed tablespace.
SQL> SELECT tablespace_name, extent_management,SEGMENT_SPACE_MANAGEMENT FROM dba_tablespaces where tablespace_name in ('TB_TPPLAN_COMPLINE','TB_TRANSACTION');
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
TB_TPPLAN_COMPLINE DICTIONARY MANUAL
TB_TRANSACTION DICTIONARY MANUAL
SQL>
- We want to know if we use the below command will it set SEGMENT_SPACE_MANAGEMENT to AUTO for the tabespaces being migrated?
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TB_TRANSACTION');
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TB_TPPLAN_COMPLINE');
--
And If we use the below command to switch back from Locally Managed tablespace to Dictionary managed tablespace and SEGMENT_SPACE_MANAGEMENT gets set to AUTO
SQL> SELECT tablespace_name, extent_management,SEGMENT_SPACE_MANAGEMENT FROM dba_tablespaces where tablespace_name in ('TB_TPPLAN_COMPLINE','TB_TRANSACTION');
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
TB_TPPLAN_COMPLINE DICTIONARY MANUAL
TB_TRANSACTION DICTIONARY MANUAL
SQL>
- We want to know if we use the below command will it set SEGMENT_SPACE_MANAGEMENT to AUTO for the tabespaces being migrated?
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TB_TRANSACTION');
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TB_TPPLAN_COMPLINE');
--
And If we use the below command to switch back from Locally Managed tablespace to Dictionary managed tablespace and SEGMENT_SPACE_MANAGEMENT gets set to AUTO
0