Renaming and Relocating Datafiles

This section describes the various aspects of renaming and relocating datafiles, and includes the following topics:

• Renaming and Relocating Datafiles for a Single Tablespace
• Renaming and Relocating Datafiles for Multiple Tablespaces

You can rename datafiles to change either their names or locations. Oracle provides options to make the following changes:

• Rename and relocate datafiles in a single offline tablespace (for example, FILENAME1 and FILENAME2 in TBSPACE1) while the rest of the database is open.
• Rename and relocate datafiles in several tablespaces simultaneously (for example, FILE1 in TBSP1 and FILE2 in TBSP2) while the database is mounted but closed.

Note: To rename or relocate datafiles of the SYSTEM tablespace, you must use the second option, because you cannot take the SYSTEM tablespace offline.

Renaming and relocating datafiles with these procedures only change the pointers to the datafiles, as recorded in the database's control file; it does not physically rename any operating system files, nor does it copy files at the operating system level. Therefore, renaming and relocating datafiles involve several steps. Read the steps and examples carefully before performing these procedures.

You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.

Renaming and Relocating Datafiles for a Single Tablespace

The following steps describe how to rename or relocate datafiles from a single tablespace.

1. Take the non-SYSTEM tablespace that contains the datafiles offline.
2. Copy the datafiles to the new location or new names using the operating system.
3. Make sure that the new, fully specified filenames are different from the old filenames.
4. Use either the Rename Datafile dialog box of Enterprise Manager/GUI or the SQL command ALTER TABLESPACE with the RENAME DATAFILE option to change the filenames within the database.

For example, the following statement renames the datafiles FILENAME1 and FILENAME2 to FILENAME3 and FILENAME4, respectively:

  ALTER TABLESPACE users
    RENAME DATAFILE 'filename1', 'filename2'
        TO 'filename3', 'filename4';

The new file must already exist; this command does not create a file. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old filename exactly as it appears in the DBA_DATA_FILES view of the data dictionary.

Renaming and Relocating Datafiles for Multiple Tablespaces

You can rename and relocate datafiles of one or more tablespaces using the SQL command ALTER DATABASE with the RENAME FILE option. This option is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation, or rename or relocate datafiles of the SYSTEM tablespace. If the database must remain open, consider instead the procedure outlined in the previous section.

To rename datafiles of several tablespaces in one operation or to rename datafiles of the SYSTEM tablespace, you must have the ALTER DATABASE system privilege.

1. Ensure that the database is mounted but closed.
2. Copy the datafiles to be renamed to their new locations and new names, using operating system commands.
3. Make sure the new copies of the datafiles have different fully specified filenames from the datafiles currently in use.
4. Use the SQL command ALTER DATABASE to rename the file pointers in the database's control file.

For example, the following statement renames the datafiles FILENAME 1 and FILENAME2 to FILENAME3 and FILENAME4, respectively:

  ALTER DATABASE
    RENAME FILE 'filename1', 'filename2'
      TO 'filename3', 'filename4';

The new file must already exist; this command does not create a file. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old filename exactly as it appears in the DBA_DATA_FILES view of the data dictionary.

Relocating Datafiles: Example

For this example, assume the following conditions:

• An open database has a tablespace named USERS that is comprised of datafiles located on the same disk of a computer.
• The datafiles of the USERS tablespace are to be relocated to a different disk drive.
• You are currently connected with administrator privileges to the open database while using Enterprise Manager.

1. Identify the datafile names of interest.

The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the USERS tablespace:

SELECT file_name, bytes FROM sys.dba_data_files
   WHERE tablespace_name = 'USERS';
FILE_NAME         BYTES
---------------------------
FILENAME1         102400000
FILENAME2         102400000

Here, FILENAME1 and FILENAME2 are two fully specified filenames, each 1MB in size.

2. Back up the database.

Before making any structural changes to a database, such as renaming and relocating the datafiles of one or more tablespaces, always completely back up the database.

3. Take the tablespace containing the datafile offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.
4. Copy the datafiles to their new locations using operating system commands. For this example, the existing files FILENAME1 and FILENAME2 are copied to FILENAME3 and FILENAME4.

Suggestion: You can execute an operating system command to copy a file without exiting Server Manager/LineMode by using the HOST command.

5. Rename the datafiles within Oracle.

The datafile pointers for the files that comprise the USERS tablespace, recorded in the control file of the associated database, must now be changed from FILENAME1 and FILENAME2 to FILENAME3 and FILENAME4, respectively.

If the tablespace is offline but the database is open, use the Enterprise Manager Rename Datafiles dialog box or ALTER TABLESPACE...RENAME DATAFILE command. If the database is mounted but closed, use the ALTER DATABASE...RENAME FILE command.

6. Bring the tablespace online, or shut down and restart the database.

If the USERS tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.

7. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

See Also: For more information about the DBA_DATA_FILES data dictionary view, see the Oracle8 Server Reference Manual.

For more information about taking a tablespace offline, see "Taking Tablespaces Offline" on page 8-8.

For more information about mounting a database without opening it, see Chapter 3, "Starting Up and Shutting Down".