Oracle ORA-00372 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00372: file cannot be modified at this time

ORA-00372 is an Oracle database error that occurs when a DML or DDL operation attempts to write to a datafile that is currently in a non-modifiable state. This error almost always appears alongside ORA-01110, which identifies the exact file causing the problem. Understanding the root cause quickly is essential, as this error can halt critical application operations.

Top 3 Causes and Fixes

1. Datafile is OFFLINE

The most common cause. A datafile can go offline due to an I/O error or manual intervention by a DBA.

-- Check the status of all datafiles
SELECT file#, name, status, recover
FROM v$datafile
ORDER BY file#;

-- Bring a datafile back online (if no recovery needed)
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' ONLINE;

-- If media recovery is required, recover first
RECOVER DATAFILE '/u01/oradata/orcl/users01.dbf';
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' ONLINE;

-- Alternatively, bring the entire tablespace online
ALTER TABLESPACE USERS ONLINE;

2. Tablespace is in READ ONLY Mode

A tablespace set to READ ONLY will block all write operations on its datafiles. This is sometimes set intentionally for archiving or migration but forgotten afterward.

-- Identify READ ONLY tablespaces
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE status = 'READ ONLY';

-- Switch the tablespace back to READ WRITE
ALTER TABLESPACE USERS READ WRITE;

-- Verify the change
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE tablespace_name = 'USERS';

3. Datafile Requires Media Recovery

After an abnormal database shutdown or disk failure, a datafile may be left in a state that requires media recovery before it can be modified.

-- Find files that need recovery
SELECT file#, name, status, recover
FROM v$datafile
WHERE recover = 'YES';

-- Perform recovery using SQL*Plus
RECOVER AUTOMATIC DATAFILE '/u01/oradata/orcl/users01.dbf';

-- Using RMAN (recommended for production)
-- RMAN> RESTORE DATAFILE '/u01/oradata/orcl/users01.dbf';
-- RMAN> RECOVER DATAFILE '/u01/oradata/orcl/users01.dbf';

-- Bring the file online after recovery
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' ONLINE;

Quick Diagnostic Query

Run this query first whenever ORA-00372 appears to identify the problem immediately.

SELECT
    df.file#,
    df.name        AS file_name,
    df.status      AS file_status,
    df.recover,
    ts.name        AS tablespace_name
FROM v$datafile df
JOIN v$tablespace ts ON df.ts# = ts.ts#
WHERE df.status != 'ONLINE'
   OR df.recover = 'YES'
ORDER BY df.file#;

Prevention Tips

1. Automate datafile status monitoring
Schedule a monitoring job using DBMS_SCHEDULER or an external cron job to check for offline or recovery-needed datafiles at least once daily. Alert the DBA team immediately when any file deviates from the ONLINE status so issues are caught before they impact end users.

2. Operate in ARCHIVELOG mode with regular RMAN backups
Always run production databases in ARCHIVELOG mode and maintain a consistent RMAN backup strategy (full + incremental). Without archived redo logs, point-in-time recovery after a media failure is impossible. Additionally, document a standard procedure requiring DBAs to revert any tablespace from READ ONLY back to READ WRITE immediately after completing planned maintenance, preventing accidental write blocks.

Related Errors

Error Code	Description
ORA-01110	Identifies the specific datafile involved — almost always appears with ORA-00372
ORA-01113	File needs media recovery
ORA-00376	File cannot be read at this time (read-side counterpart)
ORA-01114	I/O error writing block to file — often the root cause of a file going offline

📖 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.