0 like 0 dislike

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (4k points)  

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


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’,

4.       Now bring the tablespace  Online

SQL> alter tablespace users online;

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

You can rename and relocate datafiles in one or more tablespaces using the ALTER DATABASE RENAME FILE statement. This method is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation. You must have the ALTER DATABASE system privilege

To rename datafiles in multiple tablespaces, follow these steps.

1.      Ensure that the database is mounted but closed.

2.      Copy the datafiles to be renamed to their new locations and new names, using the operating system..

3.      Use ALTER DATABASE to rename the file pointers in the database control file.

For example, the following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf, respectively:

    RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
             TO '/u02/oracle/rbdb1/temp01.dbf',

Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile names exactly as they appear in the DBA_DATA_FILES view.

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

5.      Start the Database

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
0 answers
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer