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 SYSAUXDROP TABLESPACE TEMP;
— necessary if there’s no “real” temp tablespace definded yet – see Note:160426.1CREATE 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 SYSAUXEXEC 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 SYSAUXSHUTDOWN IMMEDIATE
STARTUP
What happens to SYSAUX then? Did you skip what to do with it to get the reader to think about it 🙂
Hi Edwin,
this is a good question – since this is one my oldest blog posts, I wonder whether SYSAUX always was locally managed.
But I need to create a database with dictionary managed tablespaces, then repeat the exercise.
If you have such old systems, please tell me what you did, and I happily update the blog post.
Thanks
MIke
Hi Mike
You’re right, SYSAUX is already at LOCAL