Migrate tablespace from dictionary managed to locally in Oracle

Following are the steps for Migrating the tablespace from dictionary managed to locally managed:

1. Offline all the data files.

select 'alter tablespace '||tablespace_name||' read only;' from dba_tablespaces where contents not like 'temporary' and contents not like 'undo' and tablespace_name not in ('SYSTEM');


2. Shutdown the database or instance.

SQL> shutdown immediate


3. Startup the database in restricted mode:

SQL> startup restrict


4. Migrated the system tablespace to local managed:

execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');