How to rename a datafile

How to rename/move datafile from one location to another

There is a need to relocate an Oracle datafile.

Cause and Prerequisites

You have created a datafile on the wrong filesystem or drive.
You add a new filesystem(s), drive(s) to the system and want to
redistribute the datafiles.
You restore some datafiles to a new location because the original is not
available any more ( media error )

Solution

There are several options to perform this task:

Option 1)

Connect to the open database.
Take the tablespace offline:
ALTER TABLESPACE OFFLINE;
Copy the datafiles to the new location by using OS commands.
Compare the size of the two files, they have to be the same.
Update the new datafile location with one of this commands:
- ALTER DATABASE RENAME FILE '' TO '';
- ALTER TABLESPACE RENAME DATAFILE '' TO '';
Bring the tablespace online with the command
ALTER TABLESPACE ONLINE;
Remove at OS level the original file.
You cannot use this method for the SYSTEM tablespace.

Option 2)

Stop the database.
Copy the datafiles to the new location by using OS commands.
Mount the database.
STARTUP MOUNT
Update the new datafile location:
ALTER DATABASE RENAME FILE '' TO '';
Open the database.
Remove at OS level the original file.

Option 3)

Make a trace file using the command:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Modify datafile pointers in the tracefile generated above to
reflect the changes and rename the file so the extension is "sql".
Copy the datafiles to the new locations.
Run the script created before.
Remove at OS level the original file.

NOTE: BEFORE making any structural changes to a database, such as renaming
and relocating the datafiles of one or more tablespaces, always completely
backup the database. AFTER making any structural changes to a database,
always perform an immediate and complete backup.