Today we found some really old database using really old fashion tablespaces allocation. In another life, when I began with Oracle database and we use to call MOSC as Metalink (@ludodba) there was a way to manage the extents on database called DICTIONARY MANAGED TABLESPACES (DMT), most because of performance issues, Oracle has decided to change this management of the extents instead to resides on data dictionary (SYSTEM tbs), to be stored on bitmaps on local tablespace, that’s why they called it LOCAL MANAGED TABLESPACE (LMT). This feature was introduced on Oracle 9i in 2001, almost 20 years ago and we still see old stuff running around.

The steps that we will perform are very fast and simple, but as we are changing some core things on the database, please, make sure to have full and confiable backup before.

Here is an important note, a little bit confusing, please read it before to proceed. Resuming, after convert SYSTEM, you cannot put DMT in read write mode anymore.

********* IMPORTANT NOTE **********

Note:After the SYSTEM tablespace is migrated to locally managed, any dictionary-managed tablespaces in the database cannot be made read/write.
If you want to use the dictionary-managed tablespaces in read/write mode, then Oracle recommends
that you first migrate these tablespaces to locally managed before migrating the SYSTEM tablespace.

***********************************

1) Put DATABASE in restricted session

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SELECT logins FROM v$instance;

LOGINS
---------
RESTRICTED

2) Define a default temporary tablespace on the database

SQL> col PROPERTY_NAME form a30
SQL> col PROPERTY_VALUE form a30
SQL> col DESCRIPTION form a50
SQL> select * from database_properties where PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE SYSTEM ID of default temporary tablespace

Obs.: Really bad, using system tablespace to store temporary segments, some DBA here didn’t made his homework… In fact his work.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTBS01;

Database altered.

Obs.: Check if temp is dictionary managed, if so, recreate it before to proceed with the others.

SQL> select * from database_properties where PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMPTBS01 ID of default temporary tablespace

3) Migrate all other DICTIONARY MANAGED TBS before MIGRATE system

select tablespace_name, status, extent_management 
from dba_tablespaces 
where extent_management = 'DICTIONARY';

4) Execute the migration

select 'execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('''||tablespace_name||''');' cmd 
from dba_tablespaces where extent_management = 'DICTIONARY'
and tablespace_name <> 'SYSTEM';

CMD
----------------------------------------------------------------------------------------
execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('INDX');
execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TOOLS');
execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TESTTBS1');
execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TESTTBS2');
execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('INDX');

PL/SQL procedure successfully completed.

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TOOLS');

PL/SQL procedure successfully completed.

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TESTTBS1');

PL/SQL procedure successfully completed.

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TESTTBS2');

PL/SQL procedure successfully completed.

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');

PL/SQL procedure successfully completed.

5) Make sure that all tablespaces, other than SYSTEM were migrated to LMT, after execute the migration of SYSTEM to LMT, if you left any dictionary managed tablespaces (DMT), you cannot put them read write anymore.

select tablespace_name, status, extent_management 
from dba_tablespaces 
where extent_management = 'DICTIONARY'
and tablespace_name <> 'SYSTEM';

no rows selected

6) Put all tablespaces other than UNDO, TEMP AND SYSAUX in readonly mode.

select 'ALTER TABLESPACE '||tablespace_name||' READ ONLY;' CMD
from dba_tablespaces 
where tablespace_name NOT in ('SYSTEM','SYSAUX') 
and contents not in ('UNDO','TEMPORARY');

CMD
----------------------------------------------------------
ALTER TABLESPACE INDX READ ONLY;
ALTER TABLESPACE TOOLS READ ONLY;
ALTER TABLESPACE TESTTBS1 READ ONLY;
ALTER TABLESPACE TESTTBS2 READ ONLY;
ALTER TABLESPACE USERS READ ONLY;

7) Check the status of the tablespaces

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
INDX READ ONLY
TOOLS READ ONLY
TESTTBS1 READ ONLY
TESTTBS2 READ ONLY
USERS READ ONLY
UNDOTBS01 ONLINE
TEMPTBS01 ONLINE
SYSAUX ONLINE

8) Put SYSAUX tablespace offline

SQL> alter tablespace SYSAUX offline;

Tablespace altered.

9) Check the status of tablespaces again, just UNDO, TEMP and SYSTEM must be online.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
INDX READ ONLY
TOOLS READ ONLY
TESTTBS1 READ ONLY
TESTTBS2 READ ONLY
USERS READ ONLY
UNDOTBS01 ONLINE
TEMPTBS01 ONLINE
SYSAUX OFFLINE

10) Now we can migrate SYSTEM tablespaces

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

PL/SQL procedure successfully completed.

11) Put the tablespaces again read write and online

select 'ALTER TABLESPACE '||tablespace_name||' READ WRITE;' CMD
from dba_tablespaces 
where tablespace_name NOT in ('SYSTEM','SYSAUX') 
and contents not in ('UNDO','TEMPORARY');

CMD
-----------------------------------------------------------
ALTER TABLESPACE INDX READ WRITE;
ALTER TABLESPACE TOOLS READ WRITE;
ALTER TABLESPACE USR READ WRITE;
ALTER TABLESPACE TESTTBS2 READ WRITE;
ALTER TABLESPACE USERS READ WRITE;

SQL> ALTER TABLESPACE INDX READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE TOOLS READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE USR READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE TESTTBS2 READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE USERS READ WRITE;

Tablespace altered.

12) Put SYSAUX online again

SQL> ALTER TABLESPACE SYSAUX ONLINE;

Tablespace altered.

13) Check the status of the tablespaces one more time

SQL> select tablespace_name, status, extent_management from dba_tablespaces;

TABLESPACE_NAME STATUS EXTENT_MAN
------------------------------ --------- ----------
SYSTEM ONLINE LOCAL
INDX ONLINE LOCAL
TOOLS ONLINE LOCAL
TESTTBS1 ONLINE LOCAL
TESTTBS2 ONLINE LOCAL
USERS ONLINE LOCAL
UNDOTBS01 ONLINE LOCAL
TEMPTBS01 ONLINE LOCAL
SYSAUX ONLINE LOCAL

14) Disable restricted session on the database

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

SELECT logins FROM v$instance;

LOGINS
---------
ALLOWED

If you don’t follow all steps carefully you can reach some issues like these below

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

ERROR at line 1:
ORA-10644: SYSTEM tablespace cannot be default temporary tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, UNDO, TEMP not
found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
TEMP
USERS
IDX
SYSAUX
UNDO

SQL> alter tablespace USERS read only;
SQL> alter tablespace IDX read only;

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

ERROR at line 1:
ORA-10648: Tablespace SYSAUX is not offline
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

I hope this helps you!!!

Rodrigo Mufalani and Andre Luiz Dutra Ontalba

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”