Migrating the SYSTEM Tablespace

Migrating a Temporary Tablespace

Invoke SQL*Plus.

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

SELECT tablespace_name, extent_management
FROM dba_tablespaces;

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

SELECT tablespace_name, status
FROM dba_tablespaces;

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('<tablespace_name>');

Query DBA_TABLESPACES to verify your change:

SELECT tablespace_name, status, extent_management
FROM dba_tablespaces;

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

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

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

SELECT tablespace_name
FROM dba_tablespaces
WHERE contents = 'TEMPORARY';

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

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';

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:

SELECT segment_name, status
FROM dba_rollback_segs
WHERE tablespace_name = '<tablespace_name>';

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

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

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;

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:

ALTER TABLESPACE <tablespace_name> READ ONLY;

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

SELECT logins
FROM v$instance;

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

ALTER SYSTEM ENABLE RESTRICTED SESSION;

Execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure specifying the SYSTEM tablespace:

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

Verify your change by querying DBA_TABLESPACES:

SELECT extent_management
FROM dba_tablespaces
WHERE tablespace_name = 'SYSTEM';

Issue the following command to disable RESTRICTED mode:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

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

ALTER TABLESPACE <tablespace_name> READ WRITE;

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

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

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

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.

DROP TABLESPACE <tablespace_name>

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;

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

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

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

SELECT ts#, name
FROM v$tempfile;

SELECT tablespace_name, file_name
FROM dba_temp_files;