🧩 How to Add a Datafile to a Tablespace in Oracle Database

 

🧩 How to Add a Datafile to a Tablespace in Oracle Database

Adding a new datafile is a common task for DBAs when a tablespace runs out of space. Let’s see how to do it quickly 👇

🔍 Step-by-Step Guide

1️⃣ Check tablespace size:

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

2️⃣ Add a new datafile:

ALTER DATABASE DATAFILE '/u01/oradata/PROD/users02.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

3️⃣ Verify the addition:

SELECT file_name, bytes/1024/1024 AS size_mb FROM dba_data_files WHERE tablespace_name='USERS';

✅ That’s it! You’ve successfully added a new datafile to your tablespace.

📘 DBA Tip

Always plan tablespace growth and monitor autoextend settings to avoid sudden space issues.

💬 Like this post?

👉 Follow my page for more Oracle tips and daily DBA insights!

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

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