Moving Online Datafile To a New Location in 12c

Oracle has officially announced 12c database on 10th of july . This 12 c database is having around 500 new features and according to Oracle Corp this is true cloud database. Whatever, we will see the demonstration of some new features of 12c database in coming posts . Here i am going to show you how you can move a datafile which is online .

Before 12c database you needed to make the tablespace offline while moving any of its datafile .

But from 12c release you can move datafile while its online .

SQL> col file_name for a70
SQL> set lines 200
SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                           TABLESPACE_NAME
-------------------------------------------------- ------------------
/data1/oradata/orcl/system01.dbf                       SYSTEM
/data1/oradata/orcl/sysaux01.dbf                       SYSAUX
/data1/oradata/orcl/users01.dbf                        USERS
/data1/oradata/orcl/undotbs01.dbf                      UNDOTBS1

SQL> alter database move datafile '/data1/oradata/orcl/users01.dbf' to '/data2/oradata/orcl/users01.dbf';

Database altered.

SQL>  select file_name,tablespace_name from dba_data_files;

FILE_NAME                                             TABLESPACE_NAME
---------------------------------------------------- ------------------
/data1/oradata/orcl/undotbs01.dbf                       UNDOTBS1
/data1/oradata/orcl/sysaux01.dbf                        SYSAUX
/data1/oradata/orcl/system01.dbf                        SYSTEM
/data2/oradata/orcl/users01.dbf                         USERS

This is what got recorded in alert log about this operation

alter database move datafile '/data1/oradata/orcl/users01.dbf' to '/data2/oradata/orcl/users01.dbf'
Tue Jul 16 00:42:51 2013
Moving datafile /data1/oradata/orcl/users01.dbf (6) to /data2/oradata/orcl/users01.dbf
Move operation committed for file /data2/oradata/orcl/users01.dbf
Completed: alter database move datafile '/data1/oradata/orcl/users01.dbf' to '/data2/oradata/orcl/users01.dbf'



EVEN YOU CAN MOVE SYSTEM DATAFILE TOO.......

SQL> col file_name for a70
SQL> set lines 200
SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                TABLESPACE_NAME
----------------------------------------------------- ---------------------
/data1/oradata/orcl/undotbs01.dbf                          UNDOTBS1
/data1/oradata/orcl/sysaux01.dbf                           SYSAUX
/data1/oradata/orcl/system01.dbf                           SYSTEM
/data2/oradata/orcl/users01.dbf                            USERS

SQL> alter database move datafile '/data1/oradata/orcl/system01.dbf' to '/data2/oradata/orcl/system01.dbf';

Database altered.

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                              TABLESPACE_NAME
------------------------------------------------- -------------------
/data2/oradata/orcl/system01.dbf                           SYSTEM
/data1/oradata/orcl/undotbs01.dbf                          UNDOTBS1
/data1/oradata/orcl/sysaux01.dbf                           SYSAUX
/data2/oradata/orcl/users01.dbf                            USERS

This is what got recorded in alert log about this operation


Tue Jul 16 00:51:17 2013
alter database move datafile '/data1/oradata/orcl/system01.dbf' to '/data2/oradata/orcl/system01.dbf'
Tue Jul 16 00:51:17 2013
Moving datafile /data1/oradata/orcl/system01.dbf (1) to /data2/oradata/orcl/system01.dbf
Tue Jul 16 00:52:10 2013
Move operation committed for file /data2/oradata/orcl/system01.dbf
Completed: alter database move datafile '/data1/oradata/orcl/system01.dbf' to '/data2/oradata/orcl/system01.dbf'