Oracle Distilled

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.