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 IMMEDIATESTARTUP
