The Geek Diary

How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace

by

Question: How to migrate Dictionary Managed Tablespace (DMT) to a Locally Managed Tablespace (LMT) using DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure?

Warning: It is highly recommended to test the steps in similar test environment to have an estimate of the time required for migration before implementing in Production.

You can use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a tablespace from dictionary managed to locally managed. Perform the following steps to migrate your dictionary-managed tablespaces to locally managed. The tablespace must be kept online and read/write during migration. Note that temporary tablespaces cannot be migrated. You must migrate all non-SYSTEM tablespaces prior to migrating the SYSTEM tablespace if you intend for those tablespaces to be used in READ WRITE mode.

To Migrate a Non-SYSTEM Tablespace

1. Invoke SQL*Plus.

2. Query DBA_TABLESPACES to determine the type of extent management used in each tablespace:

SQL> SELECT tablespace_name, extent_management FROM dba_tablespaces;

3. Query DBA_TABLESPACES to verify that the tablespace you want to migrate is online and in READ WRITE mode:

SQL> SELECT tablespace_name, status FROM dba_tablespaces;

4. Execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure and specify the name of the tablespace you want to migrate:

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('');

5. Query DBA_TABLESPACES to verify your change:

SQL> SELECT tablespace_name, status, extent_management FROM dba_tablespaces;

To Migrate the SYSTEM Tablespace

You can use the TABLESPACE_MIGRATE_TO_LOCAL procedure of the DBMS_SPACE_ADMIN package to migrate the SYSTEM tablespace from dictionary-managed to a locally managed tablespace. The following conditions must be met before the migration:

  • The database must have a default temporary tablespace that is not SYSTEM
  • If you are using rollback segments: There are no rollback segments in dictionary-managed tablespaces. There is at least one online rollback segment in a locally managed tablespace.
  • If you are using automatic undo management, an undo tablespace must be online
  • All tablespaces, other than the tablespace containing rollback segments or undo segments, must be in read-only mode.
  • The database must be in RESTRICTED mode

All of these conditions are enforced by the TABLESPACE_MIGRATE_TO_LOCAL procedure. You will receive an error message if any of the conditions are not met.

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 be able to use the dictionary-managed tablespaces in READ WRITE mode, you should migrate these tablespaces to locally managed before migrating the SYSTEM tablespace.

You can migrate your SYSTEM tablespace from dictionary-managed to locally managed by performing the following steps:

1. Use Recovery Manager or operating system commands to make a whole consistent (complete or full) backup of the database before you begin the migration.

2. Invoke SQL*Plus. Connect as a user with SYSDBA privileges.

3. Verify that you have a temporary tablespace other than SYSTEM by querying DBA_TABLESPACES as follows:

SQL> SELECT tablespace_name
FROM   dba_tablespaces
WHERE  contents = 'TEMPORARY';

4. If you are using rollback segments, verify that no rollback segments are in dictionary-managed tablespaces by querying DBA_SEGMENTS and DBA_TABLESPACES:

SQL> SELECT s.segment_name, s.tablespace_name, t.extent_management
FROM   dba_segments s, dba_tablespaces t
WHERE  s.tablespace_name = t.tablespace_name
AND    s.segment_type = 'ROLLBACK';

5. If you are using rollback segments, verify that there is at least one online rollback segment in a locally managed tablespace by querying DBA_ROLLBACK_SEGS:

SQL> SELECT segment_name, status
FROM dba_rollback_segs
WHERE tablespace_name = ‘[tablespace_name]’;

6. If you are using automatic undo management, verify that the undo tablespace is online by querying DBA_TABLESPACES as follows:

SQL> SELECT tablespace_name, status
FROM   dba_tablespaces
WHERE  contents = 'UNDO';

7. Verify that all tablespaces other than the ones containing rollback segments or undo segments and your temporary tablespace are READ ONLY by querying DBA_TABLESPACES as follows:

SELECT tablespace_name, status FROM dba_tablespaces;

8. If any tablespace other than the tablespace containing undo or rollback segments or the temporary tablespace is not in READ ONLY mode, issue the following command to place it in READ ONLY mode:

SQL> ALTER TABLESPACE [tablespace_name] READ ONLY;

Please note that it doesn’t work for SYSAUX, so we have to put sysaux offline.

9. Verify that the database is in RESTRICTED mode by querying V$INSTANCE:

SQL> SELECT logins FROM v$instance;

10. If the database is not in RESTRICTED mode, issue the following command to put it in RESTRICTED mode:

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

11. Execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure specifying the SYSTEM tablespace:

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

12. Verify your change by querying DBA_TABLESPACES:

SQL> SELECT extent_management
FROM   dba_tablespaces
WHERE  tablespace_name = 'SYSTEM';

13. Issue the following command to disable RESTRICTED mode:

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

14. Issue the following command for each tablespace you placed in READ ONLY mode to return it to READ WRITE:

SQL> ALTER TABLESPACE  READ WRITE;

To Migrate a Temporary Tablespace

You cannot use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a temporary tablespace that is dictionary-managed to a locally managed tablespace. You must drop the dictionary-managed temporary tablespace and re-create it as locally managed.

Because space management is much simpler and more efficient in locally managed tablespaces, they are ideally suited for temporary tablespaces. Locally managed temporary tablespaces use tempfiles, that do not modify data outside of the temporary tablespace or generate any redo for temporary tablespace data.

1. Invoke SQL*Plus and connect as a user with the CREATE TABLESPACE privilege.

2. Query the DBA_TABLESPACES view to determine the name of your dictionary-managed temporary tablespace:

SQL> SELECT tablespace_name, extent_management
FROM   dba_tablespaces
WHERE  contents = 'TEMPORARY';

3. Use the DROP TABLESPACE command to drop your dictionary-managed temporary tablespace. You can include the INCLUDING CONTENTS AND DATAFILES clause to remove the file from the operating system. Alternatively, you can remove the data file with operating system commands.

SQL> DROP TABLESPACE [tablespace_name];

4. Use the CREATE TEMPORARY TABLESPACE command to create a locally managed temporary tablespace as shown below:

CREATE TEMPORARY TABLESPACE [temp_tablespace_name]
TEMPFILE '[file_name]' SIZE 2M
AUTOEXTEND ON;

5. Query DBA_TABLESPACES view to verify the creation of your locally managed temporary tablespace:

SQL> SELECT tablespace_name, extent_management
FROM   dba_tablespaces
WHERE  contents = 'TEMPORARY';

6. Query V$TEMPFILE or DBA_TEMP_FILES to determine the names of the tempfiles:

SQL> SELECT ts#, name FROM v$tempfile;
SQL> SELECT tablespace_name, file_name FROM dba_temp_files;

You May Also Like