🧩 How to Move a Datafile to Another Location in Oracle Database

 🧩 How to Move a Datafile to Another Location in Oracle Database

Sometimes you may need to move a datafile — for example, to a new storage location, after disk reorganization, or to balance I/O load ⚙️

Let’s go through it step by step 👇

🔍 Step-by-Step Guide

1️⃣ Check the current location of the datafile:

SELECT file_id, file_name FROM dba_data_files;

2️⃣ Shutdown the database cleanly:

SHUTDOWN IMMEDIATE;

3️⃣ Move the datafile to the new location (using OS command):

mv /u01/oradata/PROD/users01.dbf /u02/oradata/PROD/users01.dbf

4️⃣ Mount the database:

STARTUP MOUNT;

5️⃣ Update the control file with the new location:

ALTER DATABASE RENAME FILE '/u01/oradata/PROD/users01.dbf'

TO '/u02/oradata/PROD/users01.dbf';

6️⃣ Open the database:

ALTER DATABASE OPEN;

✅ Done! Your datafile has been successfully moved without any data loss.

💡 DBA Tip

Always perform this during maintenance windows, and make sure to take a backup before any file-level change.

💬 Like this post?

👉 Follow my page for more Oracle DBA tips and tutorials!

👉 Facebook: https://www.facebook.com/people/Oracle-On-Linux-Tips/61581062542160/

📘 Blog: https://oracleinlinuxbyabosalma.blogspot.com