Renaming or Moving Datafiles of Oracle 11gR2 (While database in online)

This document will detail the steps to Rename OR Moving datafile using Oracle 11g R2.

The datafiles for the CREDITTESTDATA tablespace are following. We will rename following datafiles name.

SQL> select file_name from dba_data_files where tablespace_name='CREDITTESTDATA';

FILE_NAME
--------------------------------------------------------------------------------
/area_db/monthenddata01.dbf
/area_db/monthenddata02.dbf
/area_db/monthenddata03.dbf
/area_db/monthenddata04.dbf
/area_db/monthenddata05.dbf
/area_db/monthenddata06.dbf
/areadb2/monthenddata07.dbf
/area_db/monthenddata08.dbf
/area_db/monthenddata09.dbf
/areadb2/monthenddata10.dbf

10 rows selected.


Step-1: First take the tablespace offline in which the datafiles to rename or move.  

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 11:54:43 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> alter tablespace credittestdata offline;

Tablespace altered.

Step-2: Rename/move the file using operating system commands.

$ mv /area_db/monthenddata01.dbf /area_db/credittestdata01.dbf
$ mv /area_db/monthenddata02.dbf /area_db/credittestdata02.dbf
$ mv /area_db/monthenddata03.dbf /area_db/credittestdata03.dbf
$ mv /area_db/monthenddata04.dbf /area_db/credittestdata04.dbf
$ mv /area_db/monthenddata05.dbf /area_db/credittestdata05.dbf
$ mv /area_db/monthenddata06.dbf /area_db/credittestdata06.dbf
$ mv /areadb2/monthenddata07.dbf /areadb2/credittestdata07.dbf
$ mv /area_db/monthenddata08.dbf /area_db/credittestdata08.dbf
$ mv /area_db/monthenddata09.dbf /area_db/credittestdata09.dbf
$ mv /areadb2/monthenddata10.dbf /areadb2/credittestdata10.dbf

Step-3: Rename datafiles using command "ALTER DATABASE RENAME FILE".

SQL> alter database rename file '/area_db/monthenddata01.dbf' to '/area_db/credittestdata01.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata02.dbf' to '/area_db/credittestdata02.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata03.dbf' to '/area_db/credittestdata03.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata04.dbf' to '/area_db/credittestdata04.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata05.dbf' to '/area_db/credittestdata05.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata06.dbf' to '/area_db/credittestdata06.dbf';

Database altered.

SQL> alter database rename file '/areadb2/monthenddata07.dbf' to '/areadb2/credittestdata07.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata08.dbf' to '/area_db/credittestdata08.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata09.dbf' to '/area_db/credittestdata09.dbf';

Database altered.

SQL> alter database rename file '/areadb2/monthenddata10.dbf' to '/areadb2/credittestdata10.dbf';

Database altered.

SQL>


Step-4: Bring the tablespace online.

SQL> alter tablespace credittestdata online;

Tablespace altered.


SQL> select file_name from dba_data_files where tablespace_name='CREDITTESTDATA';

FILE_NAME
--------------------------------------------------------------------------------
/area_db/credittestdata01.dbf
/area_db/credittestdata02.dbf
/area_db/credittestdata03.dbf
/area_db/credittestdata04.dbf
/area_db/credittestdata05.dbf
/area_db/credittestdata06.dbf
/areadb2/credittestdata07.dbf
/area_db/credittestdata08.dbf
/area_db/credittestdata09.dbf
/areadb2/credittestdata10.dbf

10 rows selected.