ORA-01114 / ORA-01110: Cannot Add Datafile or Write to File – Causes, Fixes & Prevention (Real Oracle DBA Guide)

If you’ve worked with Oracle long enough, you’ve probably faced a moment when a database operation suddenly fails with this error:

ORA-01114: cannot add datafile to database
ORA-01110: data file n: '/u01/oradata/ORCL/users01.dbf'

It usually happens during tablespace growth, datafile creation, or checkpoint writes — and it often points to a storage issue rather than a database bug.

Let’s walk through what causes these errors, how to fix them quickly, and how to prevent them from coming back.

---

What Do ORA-01114 and ORA-01110 Mean?

• ORA-01114 tells you Oracle can’t write to a datafile — often due to a filesystem or ASM issue.
• ORA-01110 is an accompanying message that shows which file number and path triggered the error.

Together, they basically mean:

“Oracle tried to extend or write to a datafile, but the OS or disk said no.”

---

Common Causes of ORA-01114 / ORA-01110

1. Filesystem or ASM Disk Group Full

If the underlying filesystem or ASM disk group runs out of space, Oracle can’t extend a datafile.

2. Permission or Ownership Issues

Incorrect file or mount permissions can prevent Oracle from writing.

3. Corrupted or Missing Datafile

If the file is missing or inaccessible, Oracle can’t read or write to it.

4. Autoextend Disabled

If a tablespace runs out of space and autoextend is off, the next DML operation that needs more space fails.

5. I/O or Hardware Error

Sometimes it’s a low-level I/O error, disk failure, or SAN issue detected by the OS.

---

Step-by-Step Troubleshooting Guide

Follow this checklist to identify and fix the problem safely.

---

Step 1: Check the Datafile Path in the Alert Log

Your alert log will clearly show the file involved:

tail -100 $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Look for messages like:

ORA-01114: cannot add datafile to database
ORA-01110: data file 5: '/u01/oradata/ORCL/users01.dbf'

---

Step 2: Check Filesystem or ASM Free Space

For filesystem databases:

df -h /u01

For ASM:

SELECT name, total_mb, free_mb, (free_mb/total_mb*100) AS pct_free 
FROM v$asm_diskgroup;

If the space is below 10% free, extend the volume or add a new disk.

---

Step 3: Verify File Exists and Is Accessible

Use:

ls -l /u01/oradata/ORCL/users01.dbf

If missing, check your backup before restoring it.
If permissions look wrong:

chown oracle:oinstall /u01/oradata/ORCL/users01.dbf
chmod 660 /u01/oradata/ORCL/users01.dbf

---

Step 4: Check Tablespace Space Usage

Run this in SQL*Plus:

SELECT tablespace_name, file_id, file_name, autoextensible, bytes/1024/1024 AS size_mb
FROM dba_data_files
ORDER BY tablespace_name;

If autoextensible = NO, enable it:

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

---

Step 5: Add or Resize a Datafile (if full)

To resize an existing datafile:

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 4G;

To add a new datafile:

ALTER DATABASE ADD DATAFILE '/u02/oradata/ORCL/users02.dbf' SIZE 2G AUTOEXTEND ON;

---

Step 6: Check Disk I/O Health

Look for OS-level I/O errors:

dmesg | grep -i error

Or check ASM alert logs for disk group write issues:

tail -300f alert_+ASM.log

If you suspect disk corruption, engage your storage team immediately.

---

Prevention Tips (DBA Best Practices)

1. Enable AUTOEXTEND for all critical datafiles.
2. Monitor tablespace usage regularly using scripts or tools like Datadog, Prometheus, or OEM.
3. Separate data, temp, and undo tablespaces to isolate growth.
4. Set alerts for 80–90% filesystem or ASM usage.
5. Archive and purge old data periodically to reduce tablespace growth.
6. Regularly check permissions and ownerships after maintenance or migrations.

---

Real Example from the Field

Last month, a staging database crashed during a bulk load.
The alert log showed:

ORA-01114: cannot add datafile to database
ORA-01110: data file 8: '/u01/oradata/STG/sales01.dbf'

Checking the disk revealed /u01 was 100% full.
We freed 5 GB of space, resized the file, and turned on autoextend:

ALTER DATABASE DATAFILE '/u01/oradata/STG/sales01.dbf' AUTOEXTEND ON;

The load completed successfully. Lesson learned — always monitor space proactively.

---

Related Articles

• How to Fix “ORA-01111: Name for Data File Is Unknown” in Oracle Standby Databases
• How To Download And Install The Latest OPatch
• Oracle Grid Infrastructure 19c Restart Home Installation — Complete Step-by-Step Guide

---

Final Thoughts

The ORA-01114 / ORA-01110 errors may look scary, but they’re usually simple to resolve once you understand the root cause — it’s all about space, access, and configuration.

Keep your datafiles autoextending, monitor disk usage closely, and make sure permissions stay consistent.
With these best practices, you can keep your Oracle databases healthy, stable, and error-free.