Rename and Relocate Datafile | Oracle Database

Rename and Relocate a Datafile in Oracle Database

Renaming or Relocating Datafiles belonging to a Single Tablespace

You can rename datafiles to either change their names or relocate them.

To rename or relocate datafiles belonging to a Single Tablespace do the following.

1.       Take the tablespace offline

2.       Rename or Relocate the datafiles using operating system command

3.       Give the ALTER TABLESPACE with RENAME DATAFILE option to change the filenames within the Database.

4.       Bring the tablespace Online

For Example suppose you have a tablespace users with the following datafiles

        /u01/oracle/ica/usr01.dbf’
        /u01/oracle/ica/usr02.dbf’

Now you want to relocate /u01/oracle/ica/usr01.dbf’  to ‘/u02/oracle/ica/usr01.dbf’ and want to rename ‘/u01/oracle/ica/usr02.dbf’ to ‘/u01/oracle/ica/users02.dbf’  then follow the given the steps

1.       Bring the tablespace offline

SQL> alter tablespace users offline;

2.       Copy the file to new location using o/s command.

$ cp /u01/oracle/ica/usr01.dbf  /u02/oracle/ica/usr01.dbf’

Rename the file ‘/u01/oracle/ica/usr02.dbf’ to ‘/u01/oracle/ica/users02.dbf’ using o/s command.

$ mv  /u01/oracle/ica/usr02.dbf /u01/oracle/ica/users02.dbf

3.       Now start SQLPLUS and type the following command to rename and relocate these files

 SQL> alter tablespace users rename file  ‘/u01/oracle/ica/usr01.dbf’, 

 ‘/u01/oracle/ica/usr02.dbf’ to ‘/u02/oracle/ica/usr01.dbf’,

 ’/u01/oracle/ica/users02.dbf’;

4.       Now bring the tablespace  Online

SQL> alter tablespace users online;