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:
… Continue reading...STARTUP RESTRICT EXCLUSIVE;
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘TBS’);
— do this for all tablespaces except SYSTEM, TEMP and SYSAUXDROP TABLESPACE TEMP;
— necessary if there’s no “real” temp tablespace definded yet – see Note:160426.1CREATE
