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
Advertisements