Renaming or Moving a Datafile

Steps to move or rename a datafile:

1) Making the tablespace read only in which datafile lies. 


SQL> alter tablespace test read only;
Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces            
       where tablespace_name='TEST';

TABLESPACE_NAME       STATUS
------------------------------ ---------
TEST       READ ONLY

2) Moving the datafile to a different location or if required renaming it.

ORIGINAL LOCATION - /uhome/app/oracle/oradata/test01.dbf

NEW LOCATION -       /uhome/app/oradata/test02.dbf

[oracle@localhost ]$ cp /uhome/app/oracle/oradata/test01.dbf  /uhome/app/oradata/

[oracle@localhost ]$ mv /uhome/app/oradata/test01.dbf /uhome/app/oradata/test02.dbf

3) Making tablespace offline and renaming file in db.

SQL> alter tablespace test offline.

Tablespace altered.

SQL> alter database rename file '/uhome/app/oracle/oradata/test01.dbf' to '/uhome/app/oradata/test02.dbf';

Database altered.

4) Bringing back tablespace online and in read write mode.

SQL> alter tablespace test online;

Tablespace altered.

SQL> alter tablespace test read write;

Tablespace altered.

5) Removing old datafile physically from disk.

[oracle@localhost ]$ rm -f /uhome/app/oracle/oradata/test01.dbf