🧩 How to Move Datafiles to a New Location in Oracle Database

 🧩 Introduction

Sometimes you need to move datafiles due to storage changes, disk upgrades, or performance improvements. Oracle allows you to move datafiles safely with minimal downtime.

---

Step 1: Check Current Datafile Location

SELECT file_name, tablespace_name 

FROM dba_data_files

WHERE tablespace_name = 'MY_TBS';

---

Step 2: Shutdown the Database

SHUTDOWN IMMEDIATE;

---

Step 3: Move the Datafile at OS Level

mv /u01/app/oracle/oradata/ORCL/my_tbs01.dbf /u02/oradata/ORCL/my_tbs01.dbf

➡️ Adjust the source and destination paths as needed.

---

Step 4: Mount Database and Rename the Datafile

STARTUP MOUNT;

ALTER DATABASE RENAME FILE 

  '/u01/app/oracle/oradata/ORCL/my_tbs01.dbf'

TO 

  '/u02/oradata/ORCL/my_tbs01.dbf';

---

Step 5: Open the Database

ALTER DATABASE OPEN;

---

Conclusion

Moving datafiles helps with storage management and performance optimization. Always ensure you have a backup before making changes.

---