Hi everyone,

 

This article will describe how to rename and/or move Tablespaces Datafiles.

 

Put desired Tablespaces offline

SQL> alter tablespace tb1 offline normal;
SQL> alter tablespace tb2 offline normal;
SQL> alter tablespace tb3 offline normal;

 

OS side, move and rename Datafiles

# mv /previous_path/tb1.dbf /new_path/tb1_newname.dbf
# mv /previous_path/tb2.dbf /new_path/tb2_newname.dbf
# mv /previous_path/tb3.dbf /new_path/tb3_newname.dbf

 

Update file pointers in the Database control file

The following is done if you are acting on multiple Tablespaces

SQL> alter database rename file '/previous_path/tb1.dbf', '/previous_path/tb2.dbf', '/previous_path/tb3.dbf' to '/new_path/tb1_newname.dbf', '/new_path/tb2_newname.dbf', '/new_path/tb3_newname.dbf';

If only acting on one Tablespace, you can use this syntax

SQL> alter tablespace rename datafile '/previous_path/tb1.dbf', '/previous_path/tb2.dbf', '/previous_path/tb3.dbf' to '/new_path/tb1_newname.dbf', '/new_path/tb2_newname.dbf', '/new_path/tb3_newname.dbf';

 

Get Tablespaces back online

SQL> alter tablespace tb1 online;
SQL> alter tablespace tb2 online;
SQL> alter tablespace tb3 online;

 

Done.

Source : Oracle DBA Guide