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.
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.
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.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.