12c Database : Move datafile online, no offline or rename required going forward

12c Introduced the datafiles movement online, Earlier to 12c we have to

	a) offline the datafile
	b) Move the datafile to new location using os command
	c) Rename the datafile for data dictionary update
	d) Online the datafile

From 12c, its just need a command "alter database move datafile" Lets see how it works

	[oracle@Geek DBA12c admin]$ sqlplus / as sysdba

	SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 2 12:43:54 2013

	Copyright (c) 1982, 2013, Oracle.  All rights reserved.

	Connected to:
	Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
	With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

	SQL> select file_name from dba_data_files;

	FILE_NAME
	--------------------------------------------------------------------------------
	/u01/app/oracle/oradata/DB12c/system01.dbf
	/u01/app/oracle/oradata/DB12c/sysaux01.dbf
	/u01/app/oracle/oradata/DB12c/undotbs01.dbf
	/u01/app/oracle/oradata/DB12c/users01.dbf

Create new directory to hold the datafile mkdir -p /u01/app/oracle/oradata/DB12c/new

 Let's mimic some activity on users tablespace to ensure move operation wont affect the DML operation In one session

	SQL> create table t tablespace users as select * from dba_objects;

	Table created.

	SQL> begin
	  2  for i in 1..100 loop
	  3  insert into t select * from dba_objects;
	  4  end loop;
	  5  commit;
	  6  end;
	  7  /
Session insert operation in progress...... 

Open another session and move the datafile

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

	Database altered.

As you observed the insert operation above does not affecting the move operation. Lets check how does the data dictionary looks like.

	SQL> select file_name from dba_data_files;

	FILE_NAME
	--------------------------------------------------------------------------------
	/u01/app/oracle/oradata/DB12c/system01.dbf
	/u01/app/oracle/oradata/DB12c/sysaux01.dbf
	/u01/app/oracle/oradata/DB12c/new/users01.dbf
	/u01/app/oracle/oradata/DB12c/undotbs01.dbf

Another Check in OS level too

	ls -ltr /u01/app/oracle/oradata/DB12c/new/users01.dbf
	-rw-r-----. 1 oracle oinstall 5251072 Sep  2 12:45 /u01/app/oracle/oradata/DB12c/new/users01.dbf

	ls -ltr /u01/app/oracle/oradata/DB12c/
	total 1754712
	drwxr-x---. 2 oracle oinstall      4096 Sep  2 08:33 pdbseed
	-rw-r-----. 1 oracle oinstall  52429312 Sep  2 08:37 redo02.log
	drwxr-x---. 2 oracle oinstall      4096 Sep  2 08:38 pdb12c
	-rw-r-----. 1 oracle oinstall  52429312 Sep  2 10:37 redo03.log
	-rw-r-----. 1 oracle oinstall  62922752 Sep  2 12:37 temp01.dbf
	-rw-r-----. 1 oracle oinstall  89137152 Sep  2 12:42 undotbs01.dbf
	-rw-r-----. 1 oracle oinstall 807411712 Sep  2 12:45 system01.dbf
	-rw-r-----. 1 oracle oinstall 723525632 Sep  2 12:45 sysaux01.dbf
	drwxr-xr-x. 2 oracle oinstall      4096 Sep  2 12:45 new
	-rw-r-----. 1 oracle oinstall  52429312 Sep  2 12:45 redo01.log
	-rw-r-----. 1 oracle oinstall  17973248 Sep  2 12:45 control01.ctl

As you see above, the user01.dbf datafile is not in the oradata directory.

Check the table count just in case to check the table contains rows or not 😉

	SQL> select count(*) from t;

	  COUNT(*)
	----------

                    9163831

This is very cool nice feature for Level 1 support DBA's where you can reduce these steps (Offlining and copying the files at OS and rename and then online). All in one shot