How to Relocate Datafiles in Oracle?

In this article, we are going to learn how to Relocate Datafiles in Oracle from one mount point to another mount point or disk. In other words, you can say that due to insufficient disk space we need to move data files to a different location.

Read: How to drop oracle database manually

Subscribe on YouTube

Datafiles location in my case:

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/gaming/system01.dbf
/u01/app/oracle/oradata/gaming/sysaux01.dbf
/u01/app/oracle/oradata/gaming/undotbs01.dbf
/u01/app/oracle/oradata/gaming/users01.dbf
/u01/app/oracle/oradata/gaming/und01.dbf
/u01/dharma01.dbf

6 rows selected.

I want to change the location of the dharma01 file in the /u02 mount point.

Step 1. Shutdown the instance

SQL> shu immediate

Step 2. Move physical files from the old location to the new location.

$ mv /u01/dharma01.dbf /u02/dharma01.dbf

Check the file on the new location:

Step 3. Start your database at mount mode.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             822087120 bytes
Database Buffers          771751936 bytes
Redo Buffers                7344128 bytes
Database mounted.

Step 4. Now we need to change file location in controlfile using the below command.

SQL> alter database rename file '/u01/dharma01.dbf' to '/u02/dharma01.dbf';

Database altered.

Now open the database and check the location of the data file:

SQL> alter database open;

Database altered.

Check Datafile location:

This time you can see the file location has been changed.

If you want to move all data files from the current location to a new location and you have more than 100 data files, so you don't need to write the command one by one. You can use the below script to generate the command automatically.

My New location is /u02.

SQL> select distinct 'alter database rename file '''||file_name ||''' to ''/u02/' || substr(file_name,21,length(file_name)) ||''';' query from dba_data_files where file_name not like '%dharma%';

OUTPUT from the above query:

I hope now you understand how to Relocate Datafiles in Oracle.