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.

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

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

One thought on “Online Move Datafiles in Oracle 12c R1 without getting file offline

  1. Hi. do you know if we can apply this method to SYSAUX SYSTEM or UNDO tablespaces? thank you.

Leave a Reply to Jorge Cancel reply

%d bloggers like this: