Moving/Renaming Datafiles While the Database is Online

It is possible to move and or rename datafiles while the database is online provided the tablespace in which the files belong is a non SYSTEM tablespace and does not contain any active ROLLBACK or TEMPORARY segments.

This document will detail the steps to move/rename a datafile using Oracle 11g R2 on Linux. These steps also apply with 10g.

The datafile for the TEST tablespace is in the wrong directory. The file should be in /u02/app/oracle/oradata/orcl.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/orcl/users01.dbf
/u02/app/oracle/oradata/orcl/undotbs01.dbf
/u02/app/oracle/oradata/orcl/sysaux01.dbf
/u02/app/oracle/oradata/orcl/system01.dbf
/u02/app/oracle/oradata/orcl/example01.dbf
/u02/app/oracle/oradata/test/test.dbf

6 rows selected.

SQL>

The first step is to take the tablespace in which the file(s) to moved/renamed are a member offline.

[oracle@ora1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 12 09:28:58 2010

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace test offline;

Tablespace altered.

SQL>

Next we move the file using operating system commands.

SQL> host
[oracle@ora1 ~]$ mv /u02/app/oracle/oradata/test/test.dbf /u02/app/oracle/oradata/orcl/test.dbf
[oracle@ora1 ~]$ exit
exit

SQL>

Now we need to update the data dictionary and the control. We will use the ALTER DATABASE RENAME FILE statement to perform those actions.

SQL> alter database rename file '/u02/app/oracle/oradata/test/test.dbf' to '/u02/app/oracle/oradata/orcl/test.dbf';

Database altered.

SQL>

Last thing to do is bring the tablespace back online.

SQL> alter tablespace test online;

Tablespace altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/orcl/users01.dbf
/u02/app/oracle/oradata/orcl/undotbs01.dbf
/u02/app/oracle/oradata/orcl/sysaux01.dbf
/u02/app/oracle/oradata/orcl/system01.dbf
/u02/app/oracle/oradata/orcl/example01.dbf
/u02/app/oracle/oradata/orcl/test.dbf

6 rows selected.

SQL>

The move/rename is complete.

15 thoughts on “Moving/Renaming Datafiles While the Database is Online”

  1. I did try this way and this is not good way to relocation datafile, because I had issue using this way

    Thank you

  2. when i execute the HOST MOVE command it is renaming the given file,is it necessary to execute the next following command that is “ALTER DATABASE RENAME FILE”

  3. If you get this error:
    ERROR at line 1:
    ORA-01113: file XXX needs media recovery

    You need t:
    recover datafile ‘/u02/app/oracle/oradata/orcl/test.dbf’

Leave a Reply

Your email address will not be published. Required fields are marked *