Database Administration (MOSC)

MOSC Banner

How to migrate from dictionary manged tablespace to local management talespace?

edited Mar 10, 2010 1:22AM in Database Administration (MOSC) 3 commentsAnswered
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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center