Moving datafile to a different location

Moving datafile involves 4 steps:

1. Taking datafile offline
2. Moving the file to a new location
3. Recovering the datafile
4. Bringing datafile online

In order to perform the steps above, the database has to be in ARCHIVELOG mode. In order to check, run the following:

ARCHIVE LOG LIST;

The output will be something like that:

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     56
Current log sequence           58

Enable ARCHIVELOG mode and peform the following:

ALTER DATABASE DATAFILE '/u01/oradata/tbs01.dbf' OFFLINE;
ALTER DATABASE RENAME FILE '/u01/oradata/tbs01.dbf' TO '/u40/oradata/Northwind/tbs01.dbf';
RECOVER DATAFILE '/u01/oradata/Northwind/tbs01.dbf';
ALTER DATABASE DATAFILE '/u01/oradata/Northwind/tbs01.dbf' ONLINE;

before you rename the file, make sure you run the following command on the UNIX side to ensure the file on the OS has been moved:

cp /u01/oradata/tbs01.dbf /u01/oradata/Northwind/tbs01.dbf

If you do not move OS file first, you will get an error in SQL Plus while attempting to run ALTER DATABASE RENAME statement

ORA-01511: error in renaming log/data files 
ORA-01516: nonexistent log file, datafile, or tempfile "/u01/oradata/Northwind/tbs01.dbf"

When done, run the statement below to ensure that all your datafiles are in the correct place:

SELECT NAME FROM v$DATAFILE;

or

SELECT * FROM V$DBFILE; (for older versions)