Tag Archives: online datafile move

Moving/renaming datafile in Oracle database

In Oracle Database 12c moving/renaming a datafile can be done online using the following command:

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb1/system01.dbf' TO '/u02/oradata/pdb1/system01.dbf';

In older version of Oracle Database this can be done by placing the tablespace offline if the datafile is not a system datafile. Here is the sequence of commands you need to issue, tested on Oracle Database 11gR2:

select * from dba_data_files;
-- get the tablespace name and file name that you want to move
alter tablespace tbs offline;
-- move the datafile in the OS
-- mv /u01/app/oracle/oradata/orcl/test.dbf /u02/oradata/orcl/test.dbf
alter tablespace tbs rename datafile '/u01/app/oracle/oradata/orcl/test.dbf' to '/u02/oradata/orcl/test.dbf';
alter tablespace tbs online;

And that’s it. There is no need to shutdown the whole database if you need to move/rename a non system datafile.

Note: keep in mind that if you move the datafile from one partition to another, while the database is is running, the space used by the old datafile will not be released until the database is restarted because the file will appear as opened file by lsof command.