Oracle Tips : How to rename or relocation(move) datafiles

Oracle Tips : How to rename or relocation(move) datafiles.

Example case information :
- Move datafile /oracle/oradata/test01.dbf to /oracle/oradata_1/test01.dbf
- Tablespace name is 'TEST'

To rename datafiles, complete the following steps:

Take the tablespace that contains the datafiles offline. The database must be open mode.

SQL> ALTER TABLESPACE TEST OFFLINE;

Rename the datafiles using the operating system command.

cp -p /oracle/oradata/test01.dbf /oracle/oradata_1/test01.dbf

Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

SQL> ALTER DATABASE RENAME FILE '/oracle/oradata/test01.dbf' TO '/oracle/oradata_1/test01.dbf';

Take the tablespace that contains the datafiles online.

SQL> ALTER TABLESPACE TEST ONLINE;

Verify location and datafiles name.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TEST';