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

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.