Oracle: How to move tablespace data file to a different location

I added a new data file to my tablespace, and then realized that I added it to the wrong drive.

Once you add the data file, there is no way to drop it. Only option is to rename or relocated the file.

Here are the steps to relocate a data file or multiple files to a different drive.

Login as SYS

Find the current location of your tablespace

SELECT tablespace_name, FILE_NAME, BYTES,STATUS, online_status 
FROM DBA_DATA_FILES
where tablespace_name = 'MY_DATA';

Take the tablespace offline

ALTER TABLESPACE MY_DATA OFFLINE NORMAL;

You can check the status again

SELECT tablespace_name, FILE_NAME, BYTES,STATUS, online_status 
FROM DBA_DATA_FILES
where tablespace_name = 'MY_DATA'; 

Now, Move the file from your current location to new location. I am running my database on Windows, So I opened Windows explorer and cut and paste the file to the new location. You can use any OS Move command to transfer the file.

Now that the file is moved, You rename the data file location

ALTER TABLESPACE MY_DATA
 RENAME DATAFILE 'E:\APP\ORACLE\ORADATA\ORCLDB\MY_DATA2.DBF'    TO 'L:\APP\ORACLE\ORADATA\ORCLDB\MY_DATA2.DBF';

Bring the tablepace online

ALTER TABLESPACE MY_DATA ONLINE;

Check the status again

SELECT tablespace_name, FILE_NAME, BYTES,STATUS, online_status 
FROM DBA_DATA_FILES
where tablespace_name = 'MY_DATA';