Online Move Datafiles in Oracle 12c R1 without getting file offline

 

Prior to Oracle 12c, moving datafiles to another location needs downtime ( Need to Offline datafiles ) but from Oracle 12c we can accomplish same with ALTER DATABASE command.
Syntax:
ALTER DATABASE MOVE DATAFILE ( ‘filename’ | ‘ASM_filename’ | file_number )
[ TO ( ‘filename’ | ‘ASM_filename’ ) ] [ REUSE ] [ KEEP ]
Note:

• Source file would be specified with either filename, ASM_filename or file_number.
• Destination file should be specified by filename only.
• REUSE: New file should create even if it already exists.
• KEEP: Original copy of the datafile should be retained.

// Online move datafile with the help of ALTER DATABASE command + File name.
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME=’SYSTEM’;
FILE_NAME FILE_ID TABLESPACE_NAME
—————————————————- ———- ———————–
/u01/app/oracle/oradata/ORCL/datafile/system01.dbf 1 SYSTEM
SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/ORCL/datafile/system01.dbf’ TO ‘/tmp/system01.dbf’;
Database altered.
// Ensure your changes by issuing same above query, In FILE_NAME column kindly observed the location of datafile has been changed to ‘/tmp/…’
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME=’SYSTEM’;
FILE_NAME FILE_ID TABLESPACE_NAME
—————————————————- ———- ———————–
/tmp/system01.dbf 1 SYSTEM
// Online move datafile with the help of ALTER DATABASE command + File number.
SQL> ALTER DATABASE MOVE DATAFILE 1 TO ‘/u01/app/oracle/oradata/ORCL/datafile/system01.dbf’;
Database altered.
// Ensure your changes by issuing same above query:
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME=’SYSTEM’;
FILE_NAME FILE_ID TABLESPACE_NAME
—————————————————- ———- ———————–
/u01/app/oracle/oradata/ORCL/datafile/system01.dbf 1 SYSTEM

Cheers!! Now we can move our datafile without downtime with Oracle 12c.

***********************************************************************