Relocating and Renaming Datafiles in a Single Tablespace

Here is an example that illustrates the steps involved for relocating a datafile.

Assume the following conditions:

• An open database has a tablespace named users that is made up of datafiles all located on the same disk.
• The datafiles of the users tablespace are to be relocated to different and separate disk drives.
• You are currently connected with administrator privileges to the open database.
• You have a current backup of the database.

Complete the following steps:

1. Identify the datafile names of interest.

   The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the users tablespace:

   SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES
   WHERE TABLESPACE_NAME = 'USERS';
   
   FILE_NAME                                  BYTES
   ------------------------------------------ ----------------
   /U02/ORACLE/RBDB1/USERS01.DBF              102400000
   /U02/ORACLE/RBDB1/USERS02.DBF              102400000
   
   

2. Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.
3. Copy the datafiles to their new locations and rename them using the operating system.
3. You can execute an operating system command to copy a file by using the SQL*Plus HOST command.
4. Rename the datafiles within Oracle.

   The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

   If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement.

   ALTER TABLESPACE users
       RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
                       '/u02/oracle/rbdb1/users02.dbf'
                    TO '/u03/oracle/rbdb1/users01.dbf',
                       '/u04/oracle/rbdb1/users02.dbf';
   
   

5. Bring the tablespace online, or open the database.

   If the users tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.

6. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.