How To Move OR Rename An Oracle Datafile?

How to move a datafile from one location to another in Oracle:

At times , to fix the space crunch in a filesystem or to follow the filesystem standards or proper naming conventions , a DBA might have to move/rename an oracle datafile.

The datafile movement can be performed without a database restart for those tablespaces, which can be taken offline (All except System, Undo or Temp).

Lets see the steps involved in this.

Step 1: Login to the database as SYSDBA

Step 2: Make the tablespace offline

SQL> Alter tablespace <tablespace_name> offline;

Step 3: Copy the datafile to the required location at the OS level

$ cp filename1 filename2

Step 4: Rename the datafile using Alter Database command

SQL> alter database rename file < filename with the original path > to < filename with the new path >;

Step 5: Make the tablespace online.

SQL> alter tablespace <tablespace_name> online;

The above mentioned procedure is not applicable for system , temp and undo tablespaces as they cannot be taken offline. However, normally we drop and recreate the undo/temp tablespaces rather than moving the datafiles as it doesn’t involve any downtime.

Lets see how to move system datafiles.

Steps to move SYSTEM datafile from one location to another:

Step 1: Login to the database as SYSDBA

Step 2: Shutdown the database

Step 3: Copy/rename the datafile at the OS level

Step 4: Start the database at MOUNT state

Step 5: Rename the datafile using Alter Database command

Step 6: Open the database

Research and Article Contribution by : Anju