🧩 How to Drop or Delete a Datafile in Oracle Database

 🧩 How to Drop or Delete a Datafile in Oracle Database

Sometimes, as a DBA, you may need to drop or delete a datafile — for example, when you remove an unused tablespace, reclaim space, or clean up after testing.

However, deleting a datafile directly from the OS can cause serious corruption issues. Let’s see the safe way to do it 👇

⚙️ Step 1: Identify the Datafile You Want to Drop

Run this query to list all datafiles and their associated tablespaces:

SELECT file_id, file_name, tablespace_name 

FROM dba_data_files;

💡 Always double-check the file name and tablespace before deletion.

🧮 Step 2: Drop the Tablespace (Including Datafiles)

If the datafile belongs to a tablespace that’s no longer needed, drop the whole tablespace safely:

DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

✅ This removes all related datafiles cleanly from both the database and the file system.

🧠 Step 3: Delete Temp or Undo Datafiles (If Needed)

For temporary tablespaces:

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

For undo tablespaces:

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;

⚠️ Be sure another undo tablespace exists before dropping an undo one.

🔧 Step 4: Manually Remove a Datafile (If Detached)

If a datafile remains in the OS but is no longer part of the database, remove it from the filesystem using:

rm /u01/oradata/ORA/oldfile01.dbf

⚠️ Only perform this if you are 100% sure it’s no longer referenced by the database.

📊 Step 5: Verify

After deletion, confirm the datafile is removed:

SELECT file_name FROM dba_data_files;

💡 The dropped file should no longer appear in the list.

🧠 DBA Tips

Never delete datafiles directly from the OS unless they’re detached

Always back up before dropping any tablespace or datafile

Check for dependent objects before deletion

Keep track of space usage after cleanup

✨ Like & Follow my page for more Oracle DBA tips and step-by-step guides:

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

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