How to Rename Datafiles in Multiple Tablespaces All Together

How to Rename Datafiles in Multiple Tablespaces All Together

Oracle Database also provides you the ability to rename or relocate multiple datafiles simultaneously. For the same purpose, you need to use ALTER DATABASE RENAME FILE Statement. This statement helps you to rename multiple datafiles all together in one single query or one single operation.

Note - You must be provided the ALTER DATABASE privilidge in order to go ahead with this method.

Below are the steps to use this method:

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:

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;

4. 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.

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

Start the Database.