Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Renaming and Relocating Datafiles

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

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

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.

To Rename or Relocate Datafiles for a Single Tablespace

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_FILE view of the data dictionary.

Renaming and Relocating Datafiles for Multiple Tablespaces

You can rename and relocate datafiles of one or more tablespaces with 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.

To Rename and Relocate Datafiles for Multiple Tablespaces

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_FILE view of the data dictionary.

Relocating Datafiles: Example

For this example, assume the following conditions:

To Relocate Datafiles

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

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

See Also: For more information about the DBA_DATA_FILES data dictionary view, see the .

For more information about taking a tablespace offline, see "Taking Tablespaces Offline" [*].

For more information about mounting a database without opening it, see Chapter 3.


Contents Index Home Previous Next