Move datafiles to different location.

From time to time a DBA might need move an Oracle database datafile from one location to another. Reasons for this might include: I/O balancing for performance reasons, or to rectify incorrect naming standards etc. In this example we are using non-asm and windows environment. ASM would only use +DATA1 +DATA2 tag etc and for any unix os it will be “/” instead of windows tag 🙂

1. Select the current location of the datafile’s from

SQL>  SELECT NAME FROM V$DATAFILE;

2. Bring the tablespace offline

SQL> ALTER TABLESPACE USERS OFFLINE;

3.  Copy the files using OS command. In windows you can simply do copy paste to the new folder

4. Rename  datafile to new location to put  new information in the control file (Control file holds the structure of the database )

SQL> ALTER DATABASE RENAME DATAFILE ‘E:\APP\PSECC\ORADATA\SWX\SWX01.DBF’  TO  ‘F:\APP\PSECC\ORADATA\SWX\SWX01.DBF’

5. Bring the tablespace online

SQL> ALTER TABLESPACE USERS ONLINE;

Optionally if you want to delete the old files from the OS. Shutdown the database and delete the old files and bring up the database.

Author: easyoradba

Database Support Manager , Data Architect & Data Enthusiast

Leave a Reply