How Oracle Rename DataFile

Rename DataFile

The procedure of renaming an Oracle datafile is actually the same as the procedure of moving datafile with a different file name.

Let's check what tablespace owns this datafile in our case.

 SQL> select t.name tablespace_name from v$datafile d inner join v$tablespace t on d.ts# = t.ts# where d.name in ('/oradata/ORCLCDB/ORCLPDB/CRM_TBS_01.dbf');
 
 TABLESPACE_NAME
 ------------------------------
 ERP_TBS

As you can see, we erroneously named the datafile as CRM_TBS_01.dbf in the new tablespace ERP_TBS, which conflicts with our uniform naming convention of tablespace. We should correct the name into ERP_TBS_01.dbf.

CRM_TBS_01.dbf --> ERP_TBS_01.dbf

12c or Later

For 12c and later releases, like 19c, we can rename data files online simply by one SQL statement. In other words, we move it by ALTER DATABASE MOVE DATAFILE without manually taking tablespace offline.

 SQL> show user
 USER is "SYS"
 SQL> alter database move datafile '/oradata/ORCLCDB/ORCLPDB/example02.dbf' to '/oradata/TESTCDB/ORCLPDB/example02.dbf' reuse;
 
 Database altered.

11c or Earlier

For 11g and earlier release, we take the following steps to rename a datafile.

Offline Tablespace
Move DataFile
Rename DataFile
Online Tablespace

1. Offline Tablespace

We should prevent any change from the datafile by offlining the tablespace.

 SQL> alter tablespace ERP_TBS offline;
 
 Tablespace altered.
 
 SQL> select status from dba_tablespaces where tablespace_name = 'ERP_TBS';
 
 STATUS
 ---------
 OFFLINE

2. Move DataFile

We use OS command mv to rename the physical file.

 [oracle@test ~]$ cd /oradata/ORCLCDB/ORCLPDB/
 [oracle@test ORCLPDB]$ mv CRM_TBS_01.dbf ERP_TBS_01.dbf
 [oracle@test ORCLPDB]$ ll ERP_TBS_01.dbf
 -rw-r----- 1 oracle oinstall 209723392 Sep 22 22:37 ERP_TBS_01.dbf

3. Rename DataFile

Tell the database where the new location is.

 SQL> alter database rename file '/oradata/ORCLCDB/ORCLPDB/CRM_TBS_01.dbf' to '/oradata/ORCLCDB/ORCLPDB/ERP_TBS_01.dbf';
 
 Database altered.

4. Online Tablespace

We should make the tablespace go back to work.

 SQL> alter tablespace ERP_TBS online;
 
 Tablespace altered.
 
 SQL> select status from dba_tablespaces where tablespace_name = 'ERP_TBS';
 
 STATUS
 ---------
 ONLINE

The new name is working now.

There're more advanced ways to move data files, you may take a look.

Next, let's see how we rename a temp file in Oracle.