Tag Archives: locally managed

Migrate tablespace from dictionary managed to locally in Oracle

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');