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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

[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.

1 2 3 4 5 6

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.

1 2 3 4 5

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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.