12c New Features - Data file move without taking tablespace read only or offline.

12c New Features - Data file move without taking tablespace read only or offline.

one of the features of 12.1.0 is datafile rename online. so from 12.1.0 no longer we need to take tablespace offline or readonly. It can be done online.

I have tested this in normal database to understand the behaviour easily.

database version: 12.1.0

file management: file system

SQL> 1
  1* select file_name,tablespace_name,status from dba_data_files where tablespace_name='USERS'
SQL> /

FILE_NAME                             TABLESPACE_NAME            STATUS
------------------------------------------------------------ ------------------------------ ---------
/media/12c_db/oradata/ora12cdb/loc2/users02.dbf          USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/users01.dbf             USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/loc2/users03.dbf          USERS                AVAILABLE

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

TABLESPACE_NAME            STATUS
------------------------------ ---------
USERS                   ONLINE

SQL>
SQL> select * from v$version;

BANNER                               CON_ID
-------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.        0
1.0.1.0 - 64bit Production

PL/SQL Release 12.1.0.1.0 - Production                0
CORE    12.1.0.1.0    Production                    0
TNS for Linux: Version 12.1.0.1.0 - Production            0
NLSRTL Version 12.1.0.1.0 - Production                0

SQL> alter database move datafile '/media/12c_db/oradata/ora12cdb/loc2/users02.dbf' to '/media/12c_db/oradata/ora12cdb/users02.dbf';  

Database altered.

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

TABLESPACE_NAME            STATUS
------------------------------ ---------
USERS                   ONLINE

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

FILE_NAME                             TABLESPACE_NAME            STATUS
------------------------------------------------------------ ------------------------------ ---------
/media/12c_db/oradata/ora12cdb/users01.dbf             USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/loc2/users03.dbf          USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/users02.dbf             USERS                AVAILABLE
 

alert log

alter database move datafile '/media/12c_db/oradata/ora12cdb/loc2/users02.dbf' to '/media/12c_db/oradata/ora12cdb/users02.dbf' Sat Jun 29 20:58:00 2013 Moving datafile /media/12c_db/oradata/ora12cdb/loc2/users02.dbf (5) to /media/12c_db/oradata/ora12cdb/users02.dbf Move operation committed for file /media/12c_db/oradata/ora12cdb/users02.dbf Completed: alter database move datafile '/media/12c_db/oradata/ora12cdb/loc2/users02.dbf' to '/media/12c_db/oradata/ora12cdb/users02.dbf' $