Methods for Moving Datafiles

Depending on the type of tablespace, the database administrator can move datafiles using one of the following two methods :

The ALTER TABLESPACE Command
Precondition :
- Tablespace must be offline
- Target datafiles must be exist

Example of usage:
ALTER TABLESPACE userdata RENAME
DATAFILE '/u01/oradata/userdata01.dbf'
TO '/u02/oradata/userdata01.dbf';

Step to rename a datafile:
- Take the tablespace offline
- Use an OS command to move or copy the files
- Execute the ALTER TABLESPACE RENAME DATAFILES command
- Bring the tablespace online
- Use an OS command to delete the old file if necessary

The ALTER DATABASE Command
The ALTER DATABASE command can be used to move any type of datafile
Precondition:
- Database must be mounted
- Target datafile must exist

Example of usage:
ALTER DATABASE RENAME
FILE '/u01/oradata/system01.dbf'
TO '/u02/oradata/system01.dbf';

Because of the SYSTEM tablespace cannot be taken offline, you must use this method to move datafiles in the SYSTEM tablespace.

Use the following steps to rename files in tablespaces that cannot be taken offline:
- Shut down the database.
- Use an OS Command to move the files.
- Mount the database.
- Execute the ALTER DATABASE RENAME FILE command.
- Open the database