Skip to content

How Oracle Rename DataFile

  • by

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.

  1. Offline Tablespace
  2. Move DataFile
  3. Rename DataFile
  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *