11g

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