Rename datafiles

Renaming may mostly mean moving a datafile. Moving a datafile may be a need of some scenario where the OS mount/drive is running out of space (as the datafile name involves the complete path), or files management etc.

For any of the case if we want to rename a datafile we have multiple approaches.
a. Taking Database Down
b. Taking Tablespace offline
c. Taking datafile offline

Going in Detail.

a. We have to take database down in few scenarios (e.g. moving any datafile of system tablespace). The steps are:-

1. SQL> startup mount
2. Copy file to new location
3. SQL> alter database rename file '<earlier name>' to '<new name>';
4. SQL> alter database open;

b. In other case we can take any of the tablespaces offline except the SYSTEM one. The sequence and commands will be:-

1. SQL> alter tablespace <TableSpace Name> offline;
2. copy <Datafile Name> <New Location/Name >
3. SQL> alter tablespace <TableSpace Name> rename datafile '<earlier name>' to '<new name>';
4. SQL> alter tablespace <TableSpace Name> online;

c. This is the case taking least of effort, and the condition is Media Recovery should be enabled. If it is then we can directly take the datafile offline and rename it.
The command and sequence will be:-

1. SQL> alter database datafile <Datafile Name> offline;
2. copy <Datafile Name> <New Location/Name >
3. SQL> alter database rename file '<earlier name>' to '<new name>';
4. SQL> alter database datafile <Datafile Name> online;

Once we have taken the new file online, earlier can be deleted.
This may ask for recovery in any of the above cases.
And we can use recover database, recover tablespace <Tablespace Name> or recover datafile <Datafile Name> according to error message appearing at prompt.