How to Move Oracle Tablespaces and Datafiles to a New Location

Moving Tablespaces and Datafiles Offline

The process begins by taking the target datafile or tablespace offline, copying the physical file to the new location, renaming it within the database, performing any required recovery, and finally bringing the object back online.

Offline by Datafile

Take the specific datafile offline:

ALTER DATABASE DATAFILE 'D:/ORACLE/ORADATA/DBA/TEST01.DBF' OFFLINE;

Copy the physical file to the new directory (e.g., D:/TEST01.DBF).

Rename the file inside the database:

ALTER DATABASE RENAME FILE 'D:/ORACLE/ORADATA/DBA/TEST01.DBF' TO 'D:/TEST01.DBF';

Recover the datafile because an offline file requires media recovery: RECOVER DATAFILE 'D:/TEST01.DBF'; Bring the datafile online:

ALTER DATABASE DATAFILE 'D:/TEST01.DBF' ONLINE;

Offline by Tablespace

Take the tablespace offline: ALTER TABLESPACE TEST OFFLINE; Copy the associated datafile(s) to the new location.

Rename the datafile inside the tablespace:

ALTER TABLESPACE TEST RENAME DATAFILE 'D:/TEST01.DBF' TO 'D:/ORACLE/ORADATA/DBA/TEST01.DBF';

Bring the tablespace online:

ALTER TABLESPACE TEST ONLINE;

Shutdown and Restart the Database

C:> set ORACLE_SID=DBA C:> sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 29 11:14:02 2009 SQL> conn sys/admin as sysdba SQL> shutdown immediate -- Database is now shut down.

After the shutdown, copy the datafile to its new location, then restart the instance in mount mode to perform the rename:

SQL> startup mount SQL> ALTER DATABASE RENAME FILE 'D:/ORACLE/ORADATA/DBA/TEST01.DBF' TO 'D:/TEST01.DBF'; SQL> ALTER DATABASE OPEN; SQL> SELECT file#, name, status FROM v$datafile;

The query confirms that the moved file (e.g., D:/TEST01.DBF) is now ONLINE along with the other datafiles.