How To Move Datafiles Online Quickly?

Last week was the launch of my new course on Udemy! I would like to thank you all for your support! If you are interested, the course is still available on Udemy.

During the launch, one of my students, Kaley, messaged me, and it turned out he has a blog too! If interested in learning more cool stuff, check it out here: blog.tuningsql.com.

Now, let’s get back to me teaching, and you learning features of Oracle new or not, but useful ones! This week we will look at moving datafiles online, a feature available since 12c.

Do you remember the old days when we had to move datafiles, and this process had to be completed offline, it could not be done while users were accessing the datafile? Gone are those days now!

In 12c things changed, and since, datafiles can be moved online, while the users are accessing the content of the datafile.

Why would you want to move a datafile?

1) you need to place the datafile to a different location. By example: /u01/oradata/TSTDB/user_data01.dbf to /u02/oradata/TSTDB/user_data01.dbf
2) you need to rename the file, as the initial name is not correct, and you want to make the correction. By example /u01/oradata/TSTDB/user_data)01.dbf to u01/oradata/TSTDB/user_data01.dbf
3) you are moving the file to ASM
4) another reason you might have

Anyhow, the good news is that you can perform this operation online.

In order to accomplish this task, we need to use the ALTER DATABASE MOVE DATAFILE statement. You can refer to the datafile either by name, or by datafile number. To get the file name, you can query either DBA_DATA_FILES or V$DATAFILE views.
You can use the keyword: KEEP, which will move the file to the new location and keep the original file as well.
When you move a file, if a file with the same name already exists, you will get an error, unless you use the REUSE keyword. From my point of view, this keyword could be dangerous to use. What if the existing file belongs to a different tablespace, and you are overwriting it? So if I were you, I would not use the REUSE keyword, just out of caution.

So let’s get our hands dirty with some examples

–get the file names:

SQL> select file_name, file_id from dba_data_files;

C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM01.DBF 9
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF 10
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\UNDOTBS01.DBF 11
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF 12
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\LOST_PROTECTION_TBS.DBF 14

SQL> select name, file# from v$datafile;

C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM01.DBF 9
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF 10
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\UNDOTBS01.DBF 11
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF 12
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\LOST_PROTECTION_TBS.DBF 14

Let’s move the datafile from current location to new location

SQL> alter database move datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\UNDOTBS01.DBF'
to 'C:\oracle\oradata\TESTDB\testdbpdb\newlocation\users01.dbf';

SQL> select name, file# from v$datafile;

C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM01.DBF 9
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF 10
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\NEWLOCATION\USERS01.DBF 11
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF 12
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\LOST_PROTECTION_TBS.DBF 14

Oops! Just noticed I moved and renamed the UNDO datafile to users datafile by mistake. Let’s correct that and change it back to undo instead:

SQL> alter database move datafile 'C:\oracle\oradata\TESTDB\testdbpdb\newlocation\users01.dbf'
to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\NEWLOCATION\UNDOTBS01.DBF';

SQL> select name, file# from v$datafile;

C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM01.DBF 9
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF 10
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\NEWLOCATION\UNDOTBS01.DBF 11
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF 12
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\LOST_PROTECTION_TBS.DBF 14

These examples are great with regular tablespaces, but I’m interested to see if I can move system, sysaux, undo or temp datafiles online.
Let’s try that:

Q1) Can I move system and sysaux tablespace? Yes!

SQL> alter database move datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM01.DBF'
to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM.DBF';

Database altered.

SQL> alter database move datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF'
to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX.DBF';

Database altered.

Q2) Can I move undo tablespace? Yes, just demonstrated that above!

Q3) Can I move temp tablespace? Looks like not

SQL> alter database move tempfile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP01.DBF'
to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP.DBF';
alter database move tempfile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP01.DBF'
to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP.DBF'
*
ERROR at line 1:
ORA-00905: missing keyword

SQL> alter database move datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP01.DBF'
to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP.DBF';
alter database move datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP01.DBF'
to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP.DBF'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file,
or temporary file "C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP01.DBF" in the current container

To put it all together here is a mind map. Please feel free to print it and use it!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com