Move Data File Online
Since 12c, we can move data files online simply by one SQL statement. In other words, we move it by ALTER DATABASE MOVE DATAFILE.
Single-instance Database
SQL> show user
USER is "SYS"
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> alter database move datafile '/oradata/ORCLCDB/ORCLPDB/example02.dbf' to '/oradata/TESTCDB/ORCLPDB/example02.dbf' reuse;
Database altered.Or use the file number instead of the original path.
SQL> select file# from v$datafile where name = '/oradata/TESTCDB/ORCLPDB/example02.dbf';
FILE#
----------
18
SQL> alter database move datafile 18 to '/oradata/TESTCDB/ORCLPDB/example02.dbf' reuse;
Database altered.As you can see, we have changed its location online.
Please note that, for a big data file, it may take a considerable time to copy and move it. All you can do is to wait.
RAC Database
For RAC databases, we can also move data files online by the same statement.
SQL> show user
USER is "SYS"
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> alter database move datafile '+DATA/ORCLCDB/ORCLPDB/example02.dbf' to '+DATA/TESTCDB/ORCLPDB/example02.dbf' reuse;
Database altered.Explicit paths of from and to are required by such user-naming files. Let's see a special type of file naming.
Oracle Managed Files (OMF)
If you're using Oracle-managed file (OMF), you can move the file without specifying any destination. As long as DB_CREATE_FILE_DEST has been set.
DB_CREATE_FILE_DEST can be changed at session-level or system-level. Here we changed it temporarily for this session.
SQL> alter session set db_create_file_dest='+DATA2';
Session altered. Then we can simply move it.
SQL> alter database move datafile 7;
Database altered.
SQL> select file#, name from v$datafile where file# = 7;
FILE# NAME
---------- ------------------------------------------------------------
7 +DATA2/ORCL/DATAFILE/users.312.1103143591 As you can see, we have moved the data file from one directory to another. Not only location, but we can also move the data file from one disk group to another by this way.