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

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

Sometimes, you need to move datafiles to a different disk or mount point — maybe to balance storage usage, migrate to faster drives, or clean up the filesystem. Let’s walk through how to safely move Oracle datafiles step-by-step 👇

⚙️ Step 1: Identify the Datafiles

Use this query to check the current locations of all datafiles:

SELECT file#, name FROM v$datafile;

✅ Identify the datafiles that need to be moved.

⚠️ Step 2: Put the Tablespace Offline

Before moving, make the tablespace unavailable to users:

ALTER TABLESPACE users OFFLINE NORMAL;

💡 If the database uses ARCHIVELOG mode, you can use OFFLINE NORMAL.

Otherwise, take a full backup after this step.

📂 Step 3: Move the Datafile at OS Level

Now move the physical file to its new location:

mv /u01/app/oracle/oradata/ORA/users01.dbf /u02/oradata/ORA/users01.dbf

✅ Make sure the Oracle software owner has permission on the new directory.

🔧 Step 4: Update the Control File

Tell Oracle where to find the datafile after moving it:

ALTER DATABASE RENAME FILE 

'/u01/app/oracle/oradata/ORA/users01.dbf' 

TO 

'/u02/oradata/ORA/users01.dbf';

✅ This updates the internal pointer inside the control file.

🚀 Step 5: Bring the Tablespace Back Online

Once renamed successfully, bring it back online:

ALTER TABLESPACE users ONLINE;

💡 Check the alert log to ensure everything went smoothly.

🧠 DBA Tips

Always take a backup before moving any datafile

Ensure you have enough space at the new location

For ASM environments, use RMAN or ALTER DATABASE MOVE DATAFILE

Verify the new path using SELECT name FROM v$datafile;

✨ Like & Follow my page for more Oracle DBA guides & troubleshooting tips:

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

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