How To Rename Or Move A Datafile In Oracle

 

In Oracle 12c :

 

If you are in 12c version ,then renaming a datafile can be done online, without making the datafile offline.

 

SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/u01/app/oracle/oradata/ORCL/shashi.dbf                                SHASHI                         ONLINE

 

SQL>
SQL>
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCL/shashi.dbf' to '/home/oracle/shashi01.dbf';
Database altered.

 

SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/home/oracle/shashi01.dbf                                              SHASHI                         ONLINE

 

 

 

In 11g and previous versions :

 

If you are in 11g or previous version, you need to follow below steps to move or rename a datafile without shutting down the database.

 

•        First make the datafile offline.
•        move the datafile as os level.
•        Rename the datafile at database level.
•        recover the particular datafile.
•        make the datafile online.


SQL>
SQL> set lines 200 pages 100
SQL> col FILE_NAME for a70
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/u01/app/oracle/oradata/ORCL/shashi.dbf                                SHASHI                         ONLINE

 

SQL>
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/shashi.dbf' offline;
Database altered.

 

SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/u01/app/oracle/oradata/ORCL/shashi.dbf                                SHASHI                         RECOVER

 

SQL>
SQL>
SQL> !mv /u01/app/oracle/oradata/ORCL/shashi.dbf /home/oracle/shashi01.dbf

 

SQL>
SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/shashi.dbf' to '/home/oracle/shashi01.dbf';
Database altered.

 

SQL>
SQL>
SQL> alter database datafile '/home/oracle/shashi01.dbf' online;
alter database datafile '/home/oracle/shashi01.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/home/oracle/shashi01.dbf'

 

SQL>
SQL>
SQL> recover datafile 6;
Media recovery complete.

 

SQL>
SQL>
SQL>
SQL> alter database datafile '/home/oracle/shashi01.dbf' online;
Database altered.

 

SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/home/oracle/shashi01.dbf                                              SHASHI                         ONLINE

 

 

 

 

I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning… Have a great day!!!