Move to Locally Managed Tablespaces

As I’ve got asked during the workshop in Warsaw how to migrate Dictionary Managed to Locally Managed tablespaces here’s some additional information and an example.

To find out if a tablespace is dictionary or locally managed you’d use this query:

SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT from DBA_TABLESPACES;

The procedure to migrate to locally managed tablespace is:

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘tbs’);

And the whole migration procedure would look like this:

STARTUP RESTRICT EXCLUSIVE;

EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘TBS’);
— do this for all tablespaces except SYSTEM, TEMP and SYSAUX

DROP TABLESPACE TEMP;
— necessary if there’s no “real” temp tablespace definded yet – see Note:160426.1

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE ‘f’ SIZE 10M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

ALTER TABLESPACE SYSAUX OFFLINE;
ALTER TABLESPACE USERS READ ONLY;
— for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX

EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘SYSTEM’);

ALTER TABLESPACE SYSAUX ONLINE;
ALTER TABLESPACE USERS READ WRITE;
— for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX

SHUTDOWN IMMEDIATE

STARTUP