If you are in 12c version ,then renaming a datafile can be done online, without making the datafile offline.
In Oracle 12c
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='PRODUCING'; FILE_NAME TABLESPACE_NAME ONLINE_ -------------------------------------------------------- ------------------------------ ------- /home/oracle/producing1.dbf PRODUCING ONLINE SQL> SQL> alter database move datafile '/home/oracle/producing1.dbf' to '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf'; Database altered. SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='PRODUCING'; FILE_NAME TABLESPACE_NAME ONLINE_ -------------------------------------------------------- ------------------------------ ------- /home/oracle/app/oracle/oradata/cdb1/testin1.dbf PRODUCING ONLINE
Moving datafile from filesystem to ASM:
ALTER DATABASE MOVE DATAFILE '/u01/oracle/dbaclass/user1.dbf' TO '+DATA/data/dbaclass/user1.dbf';
Moving datafile from ASM to ASM:
ALTER DATABASE MOVE DATAFILE '+DATA/data/dbaclass/user1.dbf' TO '+DATA2/data/dbaclass/user1.dbf';
Moving datafile in OMF file structure
ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DATA' SCOPE=BOTH; ALTER DATABASE MOVE DATAFILE '+PRODB/MIIDB/DATAFILE/undotbs1.333.967603235';
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 SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='PRODUCING'; FILE_NAME TABLESPACE_NAME ONLINE_ -------------------------------------------------------- ------------------------------ ------- /home/oracle/app/oracle/oradata/cdb1/testin1.dbf PRODUCING ONLINE SQL> alter database datafile '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' offline; Database altered. SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='PRODUCING'; FILE_NAME TABLESPACE_NAME ONLINE_ -------------------------------------------------------- ------------------------------ ------- /home/oracle/app/oracle/oradata/cdb1/testin1.dbf PRODUCING RECOVER SQL> -- move the datafile as os level SQL> !mv /home/oracle/app/oracle/oradata/cdb1/testin1.dbf /home/oracle/producing1.dbf -- Rename the datafile at database level. SQL> alter database rename file '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' to '/home/oracle/producing1.dbf'; Database altered. -- recover the particular datafile SQL> alter database datafile '/home/oracle/producing1.dbf' online; alter database datafile '/home/oracle/producing1.dbf' online * ERROR at line 1: ORA-01113: file 37 needs media recovery ORA-01110: data file 37: '/home/oracle/producing1.dbf' SQL> recover datafile 37; Media recovery complete. -- make the datafile online; SQL> alter database datafile '/home/oracle/producing1.dbf' online; Database altered. SQL> SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='PRODUCING'; FILE_NAME TABLESPACE_NAME ONLINE_ -------------------------------------------------------- ------------------------------ ------- /home/oracle/producing1.dbf PRODUCING ONLINE