Renaming and Relocating Datafiles

You can rename datafiles to either change their names or relocate them. Some possible procedures for doing this are described in the following sections:

Procedures for Renaming and Relocating Datafiles in a Single Tablespace
Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

When you rename and relocate datafiles with these procedures, only the pointers to the datafiles, as recorded in the database control file, are changed. The procedures do not physically rename any operating system files, nor do they copy files at the operating system level. Renaming and relocating datafiles involves several steps. Read the steps and examples carefully before performing these procedures.

Procedures for Renaming and Relocating Datafiles in a Single Tablespace

The section suggests some procedures for renaming and relocating datafiles that can be used for a single tablespace. You must have ALTER TABLESPACE system privileges.

Procedure for Renaming Datafiles in a Single Tablespace

To rename datafiles in a single tablespace, complete the following steps:

Take the tablespace that contains the datafiles offline. The database must be open.

For example:
```
ALTER TABLESPACE users OFFLINE NORMAL;
```
Rename the datafiles using the operating system.
Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf, respectively:
```
ALTER TABLESPACE users
    RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
                    '/u02/oracle/rbdb1/user2.dbf'
                 TO '/u02/oracle/rbdb1/users01.dbf', 
                    '/u02/oracle/rbdb1/users02.dbf';
```
Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Procedure for Relocating Datafiles in a Single Tablespace

Here is a sample procedure for relocating a datafile.

Assume the following conditions:

An open database has a tablespace named users that is made up of datafiles all located on the same disk.
The datafiles of the users tablespace are to be relocated to different and separate disk drives.
You are currently connected with administrator privileges to the open database.
You have a current backup of the database.

Complete the following steps:

If you do not know the specific file names or sizes, you can obtain this information by issuing the following query of the data dictionary view DBA_DATA_FILES:

SQL> SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES
  2> WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME                                  BYTES
------------------------------------------ ----------------
/u02/oracle/rbdb1/users01.dbf              102400000
/u02/oracle/rbdb1/users02.dbf              102400000

Take the tablespace containing the datafiles offline:
```
ALTER TABLESPACE users OFFLINE NORMAL;
```
Copy the datafiles to their new locations and rename them using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in "Copying Files Using the Database Server".

Note:
You can temporarily exit SQL*Plus to execute an operating system command to copy a file by using the SQL*Plus HOST command.
Rename the datafiles within the database.

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 the old names to the new names.

Use the ALTER TABLESPACE...RENAME DATAFILE statement.
```
ALTER TABLESPACE users
    RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
                    '/u02/oracle/rbdb1/users02.dbf'
                 TO '/u03/oracle/rbdb1/users01.dbf', 
                    '/u04/oracle/rbdb1/users02.dbf';
```
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

You can rename and relocate datafiles in one or more tablespaces using the ALTER DATABASE RENAME FILE statement. This method is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation. You must have the ALTER DATABASE system privilege.

Note:

To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.

To rename datafiles in multiple tablespaces, follow these steps.

Ensure that the database is mounted but closed.

Note:
Optionally, the database does not have to be closed, but the datafiles (or tempfiles) must be offline.
Copy the datafiles to be renamed to their new locations and new names, using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in "Copying Files Using the Database Server".
Use ALTER DATABASE to rename the file pointers in the database control file.

For example, the following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf, respectively:
```
ALTER DATABASE
    RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
                '/u02/oracle/rbdb1/user3.dbf'
             TO '/u02/oracle/rbdb1/temp01.dbf',
                '/u02/oracle/rbdb1/users03.dbf;
```
Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile names exactly as they appear in the DBA_DATA_FILES view.
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.