How to Renaming and Relocating Datafiles

1. Procedures for Renaming and Relocating Datafiles in a Single Tablespace

   A. Take the tablespace that contains the datafiles offline. The database must be open
      e.g.  ALTER TABLESPACE users OFFLINE NORMAL
   B. Rename the datafiles using the operating system
      e.g. cp 'c:\test.dbf' to 'd:\test01.dbf';
   C. Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.
      e.g. ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf','/u02/oracle/rbdb1/user2.dbf'
           TO '/u02/oracle/rbdb1/users01.dbf','/u02/oracle/rbdb1/users02.dbf';
   D. alter tablespace xx online
   E. Back up the database. After making any structural changes to a database, always perform. an immediate and complete backup.
  
  
2. Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

   A. Ensure that the database is mounted but closed
  
   B. Copy the datafiles to be renamed to their new locations and new names
  
   C. Use ALTER DATABASE to rename the file pointers in the database control file
      ALTER DATABASE RENAME FILE '/u02/oracle/rbdb1/sort01.dbf','/u02/oracle/rbdb1/user3.dbf'
      TO '/u02/oracle/rbdb1/temp01.dbf','/u02/oracle/rbdb1/users03.dbf';
   D. alter database open
   E. Back up the database. After making any structural changes to a database, always perform. an immediate and complete backup

Note: To rename or relocate datafiles of the SYSTEM tablespace,the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.  

e.g.
SQL> conn sys/oracle@b as sysdba
已连接。
SQL> create tablespace tbs datafile 'c:/tbs.dbf' size 1m;
表空间已创建。
SQL> create table test(id char(1)) tablespace tbs;
表已创建。
SQL> insert into test values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select file_name , tablespace_name, status from dba_data_files
  2  where tablespace_name = 'TBS';
FILE_NAME  TABLESPACE_NAME                STATUS
---------- ------------------------------ ---------
C:\TBS.DBF TBS                            AVAILABLE

SQL> select tablespace_name , status from dba_tablespaces
  2  where tablespace_name = 'TBS'
TABLESPACE_NAME                STATUS
------------------------------ ---------
TBS                            ONLINE
SQL> alter tablespace tbs offline;
表空间已更改。
SQL>  select tablespace_name , status from dba_tablespaces
  2   where tablespace_name = 'TBS';
TABLESPACE_NAME                STATUS
------------------------------ ---------
TBS                            OFFLINE
SQL>  select file_name , tablespace_name, status from dba_data_files
  2  where tablespace_name = 'TBS';
FILE_NAME  TABLESPACE_NAME                STATUS
---------- ------------------------------ ---------
C:\TBS.DBF TBS                            AVAILABLE

SQL> ALTER TABLESPACE TBS RENAME DATAFILE 'C:\TBS.DBF' TO 'D:\TBS.DBF';
数据库已更改。

SQL> ALTER TABLESPACE TBS ONLINE;
表空间已更改。

SQL> select tablespace_name , status from dba_tablespaces
  2   where tablespace_name = 'TBS';
TABLESPACE_NAME                STATUS
------------------------------ ---------
TBS                            ONLINE

SQL> SELECT * FROM TEST;
I
-
1

参考文献：

Oracle® Database
Administrator's Guide
10g Release 2 (10.2)
B14231-02 (255page)