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.