🧩 How to Resize a Datafile in Oracle Database

 🧩 How to Resize a Datafile in Oracle Database

As your database grows, tablespaces might run out of space or have unused space that can be reclaimed. Managing datafile size efficiently helps maintain performance and disk space balance. Let’s see how to resize a datafile step-by-step 👇

⚙️ Step 1: Check the Current Size of Datafiles

Run this query to identify the current size and usage of datafiles:

SELECT file_name, bytes/1024/1024 AS size_mb FROM dba_data_files;

✅ This gives you the size of each datafile in MB.

🧮 Step 2: Check Free Space in the Tablespace

Before resizing, check the amount of free space available:

SELECT tablespace_name, file_id, block_id, bytes/1024/1024 AS free_mb 

FROM dba_free_space 

ORDER BY tablespace_name;

💡 Helps you determine whether you can safely reduce the file size.

🔧 Step 3: Resize the Datafile

To increase the datafile size:

ALTER DATABASE DATAFILE '/u01/oradata/ORA/users01.dbf' RESIZE 2000M;

To decrease the datafile size:

ALTER DATABASE DATAFILE '/u01/oradata/ORA/users01.dbf' RESIZE 1000M;

⚠️ You can only shrink the file if free space exists at the end of the datafile.

🧠 Step 4: Automatically Extend Datafiles (Optional)

Enable autoextend to grow the file automatically when needed:

ALTER DATABASE DATAFILE '/u01/oradata/ORA/users01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

✅ This prevents space-related errors during future growth.

📊 Step 5: Verify the Changes

Recheck the size to confirm the resize operation:

SELECT file_name, bytes/1024/1024 AS size_mb FROM dba_data_files;

💡 Compare results before and after resizing to ensure success.

🧠 DBA Tips

Always ensure there’s enough disk space before increasing a file size

Avoid shrinking datafiles frequently to reduce fragmentation

Enable autoextend only for critical tablespaces

Schedule regular monitoring for tablespace usage

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

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

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