It’s fantastic new feature in 12C. In this release, a data file can now be moved online while it is open and being accessed.

alter database move datafile 
'<file_to_move_old_location>' to '<file_to_move_new_location';

Example of potential use of it:

• Move the data files from one type of storage to another
• Move data files that are accessed infrequently to lower cost storage
• Make a tablespace read-only and move its data files to write-once storage
• Move a database into Oracle Automatic Storage Management (Oracle ASM)

Let’s create sample tablespace

create tablespace test_tblsp
datafile 
'D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\TEST_TBLSP01.DBF' 
size 200m;

and test table in it

create table test_tbl
(
  id1 number
) tablespace test_tblsp;

insert into test_tbl values(1);

commit;

verify segment and data file status

select segment_name, segment_type, tablespace_name, bytes
from user_segments where segment_name='TEST_TBL';

SEGMENT_NAME SEGMENT_TYPE  TABLESPACE_NAME  BYTES
------------ ------------- ---------------- ----------
TEST_TBL     TABLE         TEST_TBLSP       65536 

select file_name, status 
from dba_data_files
where tablespaec_name='TEST_TBLSP';

FILE_NAME                                               STATUS  
------------------------------------------------------- ---------
D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\TEST_TBLSP01.DBF  AVAILABLE

now it’s time to move online datafile to new location

alter database move datafile 
'D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\TEST_TBLSP01.DBF'
to
'D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\TEST_TBLSP02.DBF';

database move DATAFILE altered.

select file_name, status
from dba_data_files
where tablespaec_name='TEST_TBLSP';

FILE_NAME                                               STATUS  
------------------------------------------------------- ---------
D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\TEST_TBLSP02.DBF  AVAILABLE

we still have access to table during MOVE DATAFILE operation

select * from test_tbl;

       ID1
----------
         1

NOTE – datafile in old location is automatically removed – exception is windows where you need to do it manually.

In other OS if you want to preserve datafile in old location you need to use KEEP clause

alter database move datafile 
'<file_to_move_old_location>'
to
'<file_to_move_new_location' keep;

in case file already exists in new location you can use option REUSE to overwrite it. Be careful with this option to not overwrite file which is in use by other database ;).

alter database move datafile 
'<file_to_move_old_location>'
to
'<file_to_move_new_location' reuse;

ALTER DATABASE MOVE DATAFILE can’t be used for:

• temporary data files – so add new file in new location and drop in old one like in pre-12c releases
• redo log files – so add new group in new location and drop in old one like in pre-12c releases

To remember:

• move datafile operations on primary are not affecting standby database and vice versa
• flashback operation does not relocate a moved data file to its previous location

Have a fun 🙂

Tomasz