I want to share with you a very good new feature in 12c , you can now move “datafiles” ONLINE while applications are performing DML operations.

In 11g we used to put tablespace offline, then use the operating system “mv” command to move the datafile from one directory to another then use the command:

ALTER TABLESPACE RENAME ‘/orad08/oradbd14/DUMMY_SM.ORA’ TO ‘/orad07/oradbd14/DUMMY_SM.ORA’;

The drawback was that there is an outage while performing this operation.

Now, in 12c you can perform this operation ONLINE whit no issues.

To illustrate:

A tablepsace TS_DUMMY is currently having a datafile under orad07 directory

Executing the command:

SQL> ALTER DATABASE MOVE DATAFILE ‘/orad07/oradbd14/DUMMY_SM.ORA’ TO ‘/orad10/oradbd14/DUMMY_SM.ORA’;

NOW the data file has been relocated after the successful execution of the command:

Another option is using the same command with the keyword “KEEP” which copies the datafile to the destination directory but keeps the datafile in original directory. However, the default tablespace will reflect the new moved data file !

SQL> ALTER DATABASE MOVE DATAFILE ‘/orad10/oradbd14/DUMMY_SM.ORA’ TO ‘/orad09/oradbd14/DUMMY_SM.ORA’ KEEP;

2 datafiles exist in both directories

Default datafile will be the moved one

You can’t do that with TEMP datafile or Archive,…etc