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

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.