🧩 How to Drop or Delete a Datafile in Oracle Database

 🧩 How to Drop or Delete a Datafile in Oracle Database

Sometimes, as part of storage reorganization or database cleanup, you may need to drop an unused datafile from a tablespace. However, it’s important to do this safely to avoid corrupting data or impacting performance. Let’s go step by step 🧠

🪄 1️⃣ Step 1: Identify the Datafile

List all datafiles and their associated tablespaces:

SQL> SELECT file_id, file_name, tablespace_name, bytes/1024/1024 AS size_mb 

FROM dba_data_files 

ORDER BY tablespace_name;

👉 This helps you confirm which datafile you intend to remove.

🪄 2️⃣ Step 2: Check If the Datafile Is Empty

Before dropping, make sure no objects exist in that datafile:

SQL> SELECT segment_name, tablespace_name, file_id 

FROM dba_extents 

WHERE file_id = <your_file_id>;

💡 If no rows are returned, it’s safe to drop the file.

🪄 3️⃣ Step 3: Drop the Datafile

Use the following command to remove the datafile from the tablespace:

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD/users02.dbf' OFFLINE DROP;

⚠️ This marks the file as dropped but doesn’t remove it from the OS yet.

🪄 4️⃣ Step 4: Remove the File from the Operating System

After confirming it’s no longer used by Oracle:

$ rm /u01/app/oracle/oradata/PROD/users02.dbf

🪄 5️⃣ Step 5: Verify

Finally, check that the file is gone from the database view:

SQL> SELECT file_name FROM dba_data_files;

✅ You’ve now safely dropped a datafile that was no longer needed — improving space management and organization in your Oracle database.

👍 Like & Follow My Page for More Oracle Tips:

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

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