Oracle 12c new feature: Move datafile online

 

Oracle 12c new feature: Move datafile online

Prior to 12c, we have to put tablespace offline to move datafiles to new location and also we need to manually copy the file.

From 12c, it will be done automatically through online mode

Eg: Moving users tablespace datafile to different name at different location when it was online

SQL> select tablespace_name, file_name, status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
USERS
/u01/app/oracle/oradata/NONCDB/datafile/users01.dbf
AVAILABLE


SQL> !ls -lrt /u01/app/oracle/oradata/NONCDB/datafile          
total 2736412
-rw-r-----. 1 oracle oinstall  91234304 Nov  3 06:45 o1_mf_temp_8x845rb3_.tmp
-rw-r-----. 1 oracle oinstall 450895872 Nov  3 06:50 o1_mf_undotbs1_8x843pdh_.dbf
-rw-r-----. 1 oracle oinstall 838868992 Nov  3 06:50 o1_mf_system_8x83zqv4_.dbf
-rw-r-----. 1 oracle oinstall 880812032 Nov  3 06:50 o1_mf_sysaux_8x83wgfw_.dbf
-rw-r-----. 1 oracle oinstall 627843072 Nov  3 06:53 users01.dbf

SQL> alter database move datafile '/u01/app/oracle/oradata/NONCDB/datafile/users01.dbf' to '/u01/app/oracle/oradata/NONCDB/users_renamed01.dbf';

Database altered.

SQL> !ls -lrt /u01/app/oracle/oradata/NONCDB/datafile
total 2123280
-rw-r-----. 1 oracle oinstall  91234304 Nov  3 06:45 o1_mf_temp_8x845rb3_.tmp
-rw-r-----. 1 oracle oinstall 450895872 Nov  3 06:50 o1_mf_undotbs1_8x843pdh_.dbf
-rw-r-----. 1 oracle oinstall 838868992 Nov  3 06:50 o1_mf_system_8x83zqv4_.dbf
-rw-r-----. 1 oracle oinstall 880812032 Nov  3 06:50 o1_mf_sysaux_8x83wgfw_.dbf

SQL> !ls -lrt /u01/app/oracle/oradata/NONCDB
total 633640
drwxr-x---. 2 oracle oinstall      4096 Jul  3  2013 controlfile
drwxr-x---. 2 oracle oinstall      4096 Jul  3  2013 onlinelog
-rw-r-----. 1 oracle oinstall  10493952 Nov  3 06:44 ilm1.dbf
-rw-r-----. 1 oracle oinstall  10493952 Nov  3 06:44 ilm2.dbf
-rw-r-----. 1 oracle oinstall 627843072 Nov  3 06:54 users_renamed01.dbf
drwxr-x---. 2 oracle oinstall      4096 Nov  3 06:54 datafile

SQL> select tablespace_name, file_name, status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
USERS
/u01/app/oracle/oradata/NONCDB/users_renamed01.dbf
AVAILABLE