DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01119 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01119: Error in Creating Database File — Causes, Fixes & Prevention

ORA-01119 is an Oracle error that occurs when the database engine fails to create a physical datafile on the operating system level. It is most commonly triggered when adding a datafile to an existing tablespace or creating a new one, and it almost always appears alongside companion errors such as ORA-27040 or ORA-27041 that provide the underlying OS-level detail. Understanding the full error stack is essential for a fast and accurate resolution.

Top 3 Causes

1. Insufficient Disk Space

The most frequent cause. When the target filesystem has no room left, the OS rejects the file creation request.

Diagnosis:

-- Check datafile sizes and autoextend settings
SELECT
    tablespace_name,
    file_name,
    ROUND(bytes / 1024 / 1024, 2)     AS size_mb,
    ROUND(maxbytes / 1024 / 1024, 2)  AS max_size_mb,
    autoextensible
FROM dba_data_files
ORDER BY tablespace_name;
Enter fullscreen mode Exit fullscreen mode

Fix — add a datafile on a volume with free space:

ALTER TABLESPACE users
ADD DATAFILE '/oradata2/PROD/users02.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 2G;
Enter fullscreen mode Exit fullscreen mode

2. OS Permission / Ownership Problem

The oracle OS user lacks write permission on the target directory, or the directory was created under a different owner after a new mount point or storage expansion.

Fix — correct permissions at the OS level (run as root):

# Verify ownership and permissions
ls -ld /oradata/PROD/

# Fix ownership and permissions
chown oracle:oinstall /oradata/PROD/
chmod 755 /oradata/PROD/
Enter fullscreen mode Exit fullscreen mode

Verify Oracle directory object grants if applicable:

-- Check directory object privileges
SELECT grantee, table_name AS dir_name, privilege
FROM dba_tab_privs
WHERE table_name IN (SELECT directory_name FROM dba_directories);

-- Grant write access if missing
GRANT READ, WRITE ON DIRECTORY oradata_dir TO oracle;
Enter fullscreen mode Exit fullscreen mode

3. Invalid or Non-Existent File Path

A typo in the path, a missing directory, or a misconfigured DB_CREATE_FILE_DEST parameter will prevent Oracle from creating the file.

Diagnosis:

-- Check the default file destination parameter
SHOW PARAMETER db_create_file_dest;
Enter fullscreen mode Exit fullscreen mode

Fix — create the missing directory (OS) then retry:

mkdir -p /oradata/PROD/new_location
chown oracle:oinstall /oradata/PROD/new_location
chmod 755 /oradata/PROD/new_location
Enter fullscreen mode Exit fullscreen mode

Fix — correct the parameter and recreate the tablespace:

ALTER SYSTEM SET db_create_file_dest = '/oradata/PROD/'
SCOPE = BOTH;

CREATE TABLESPACE new_tbs
DATAFILE '/oradata/PROD/new_tbs01.dbf'
SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  1. Read the full error stack — ORA-27040/27041 will tell you the exact OS error code.
  2. Run df -h on the target mount point to rule out disk-full conditions.
  3. Confirm the directory exists and is owned/writable by the oracle OS user.
  4. Double-check the path for typos before reissuing the DDL statement.
  5. Review DB_CREATE_FILE_DEST if you are relying on Oracle Managed Files (OMF).

Prevention Tips

Monitor tablespace usage proactively. Schedule the query below as a cron job or OEM alert to catch filesystems approaching capacity before they cause outages.

-- Alert when tablespace usage exceeds 85%
SELECT
    a.tablespace_name,
    ROUND((1 - NVL(b.free_mb,0) / a.total_mb) * 100, 2) AS used_pct
FROM (
    SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
    FROM dba_data_files GROUP BY tablespace_name
) a
LEFT JOIN (
    SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
    FROM dba_free_space GROUP BY tablespace_name
) b ON a.tablespace_name = b.tablespace_name
WHERE ROUND((1 - NVL(b.free_mb,0) / a.total_mb)*100,2) >= 85
ORDER BY used_pct DESC;
Enter fullscreen mode Exit fullscreen mode

Standardize directory structure and enforce MAXSIZE. Define a naming convention and directory layout for all datafiles, document it, and always set a MAXSIZE on AUTOEXTEND to prevent any single tablespace from consuming an entire filesystem. In RAC environments, verify that every node shares the same valid path before issuing storage DDL.

Related Oracle Errors

Error Code Brief Description
ORA-27040 File create error — OS-level detail accompanying ORA-01119
ORA-27041 Unable to open file — path or permission issue
ORA-01110 Identifies the specific datafile number and name involved
ORA-01116 Error opening database file — file missing or inaccessible
ORA-19502 Write error on RMAN file — similar filesystem-level root cause

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)