Move Datafiles To Another Location

This document is useful for DBAs to move datafiles to another location to avoid space problems.

A- Show all datafiles in my database
SQL> select name from v$datafile;
NAME
——————————————————————————–
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

B- As we face a space problem in D: drive,we will move all datafiles inside the USERS tablespace to another location
1- First we will shutdown the database
SQL> SHUTDOWN IMMEDIATE

2- Copy the datafiles inside the USERS tablespace to another location
E:\> move D:\oracle\product\10.2.0\oradata\ORCL\USERS01.DBF E:\oradata

3- Startup the database in Mount mode
SQL> STARTUP MOUNT

4- Rename the datafile to the new location in the database Controlfile
SQL> alter database rename file ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF’ to ‘E:\oradata\users01.dbf’;

4- Open the database
SQL> ALTER DATABASE OPEN;

5- Check that the move was done successfully
SQL> select name from v$datafile;
NAME
——————————————————————————–
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
E:\ORADATA\USERS01.DBF