Error
Error Code: ORA-30156

Oracle Error ORA-30156: Out-of-Disk

📦 Oracle Database
📋

Description

The ORA-30156 error indicates that the Oracle database cannot write data to disk because the file system it relies on is full. This typically occurs during data insertion, update operations, or when the database attempts to create temporary files.
💬

Error Message

ORA-30156: Out of disk space
🔍

Known Causes

4 known causes
⚠️
Full Data Tablespace
The tablespace where database tables are stored has reached its maximum size limit.
⚠️
Insufficient Disk Space
The physical disk hosting the database files is completely full, preventing further writes.
⚠️
Archivelog Destination Full
The directory designated for storing archived redo logs has run out of space.
⚠️
Temporary Tablespace Exhaustion
The temporary tablespace used for sorting and other operations has reached its maximum size.
🛠️

Solutions

4 solutions available

1. Identify and Free Up Space in Datafiles medium

Locate large datafiles and remove unnecessary objects or shrink them.

1
Connect to the Oracle database as a user with DBA privileges.
2
Query the `DBA_DATA_FILES` view to identify the largest datafiles and their current size and free space.
SELECT file_name, bytes / 1024 / 1024 AS size_mb, (bytes - NVL(SUM(bytes), 0)) / 1024 / 1024 AS free_mb
FROM dba_data_files df
LEFT JOIN dba_free_space fs ON df.file_id = fs.file_id
GROUP BY df.file_name, df.bytes
ORDER BY size_mb DESC;
3
Based on the output, identify large datafiles that might contain unused space. You can then consider dropping unused tables, indexes, or partitions. If dropping is not an option, you can shrink the datafile.
4
To shrink a datafile (use with caution, ensure you have enough free space within the file before shrinking):
ALTER DATABASE DATAFILE '<full_path_to_datafile>' RESIZE <new_size_in_MB>M;
5
Alternatively, if you have identified specific objects (tables, indexes) that are consuming space and are no longer needed, you can drop them.
DROP TABLE schema_name.table_name;
DROP INDEX schema_name.index_name;

2. Add a New Datafile easy

Extend the tablespace by adding a new datafile to accommodate more data.

1
Connect to the Oracle database as a user with DBA privileges.
2
Identify the tablespace that is running out of space. You can use the query from Solution 1 and look at the `tablespace_name` column if you join with `dba_tablespaces` or infer it from the datafile path.
3
Add a new datafile to the affected tablespace. Ensure you have sufficient disk space on the server where the new datafile will be created.
ALTER TABLESPACE <tablespace_name> ADD DATAFILE '<path_to_new_datafile>' SIZE <initial_size_in_MB>M AUTOEXTEND ON NEXT <increment_size_in_MB>M MAXSIZE UNLIMITED;
4
Replace `<tablespace_name>`, `<path_to_new_datafile>`, `<initial_size_in_MB>`, and `<increment_size_in_MB>` with appropriate values. `MAXSIZE UNLIMITED` is often used, but consider setting a reasonable limit if disk space is a concern.

3. Extend Existing Datafile Automatically easy

Configure datafiles to automatically grow when they reach their current limit.

1
Connect to the Oracle database as a user with DBA privileges.
2
Query `DBA_DATA_FILES` to check the `AUTOEXTENSIBLE` and `MAXBYTES` columns for the relevant datafiles.
SELECT file_name, autoextensible, maxbytes / 1024 / 1024 AS max_mb, increment_by / 1024 / 1024 AS increment_mb
FROM dba_data_files
WHERE file_id IN (SELECT file_id FROM dba_free_space GROUP BY file_id HAVING SUM(bytes) < 1024*1024*100); -- Example: find files with less than 100MB free space
3
If `AUTOEXTENSIBLE` is 'NO', or if `MAXBYTES` is too restrictive, you can alter the datafile to enable autoextend or increase the maximum size.
ALTER DATABASE DATAFILE '<full_path_to_datafile>' AUTOEXTEND ON NEXT <increment_size_in_MB>M MAXSIZE <new_max_size_in_MB>M;
4
Ensure that the server's underlying file system has enough free space to accommodate the `MAXSIZE` you set. If `MAXSIZE` is `UNLIMITED`, the file system will be the ultimate limit.

4. Manage Temporary Tablespace medium

Address space issues in the temporary tablespace, which is used for sorting, hashing, and other operations.

1
Connect to the Oracle database as a user with DBA privileges.
2
Check the usage of the temporary tablespace.
SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb, SUM(CASE WHEN free_bytes IS NULL THEN 0 ELSE free_bytes END) / 1024 / 1024 AS free_mb
FROM v$temp_space_header
GROUP BY tablespace_name;
3
If the temporary tablespace is full or nearing capacity, you can add a new temporary datafile.
ALTER TABLESPACE <temp_tablespace_name> ADD TEMPFILE '<path_to_new_tempfile>' SIZE <initial_size_in_MB>M AUTOEXTEND ON NEXT <increment_size_in_MB>M MAXSIZE UNLIMITED;
4
Alternatively, if you have multiple temporary tablespaces, you can switch the default temporary tablespace for users to one with more space.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <new_default_temp_tablespace_name>;
5
For specific sessions that might be consuming excessive temporary space, you can identify them and potentially kill them (use with caution).
SELECT s.sid, s.serial#, s.username, s.program, t.sql_id, t.blocks * ts.block_size / 1024 / 1024 AS temp_mb
FROM v$tempseg_usage t, v$session s, dba_tablespaces ts
WHERE t.session_id = s.sid AND ts.tablespace_name = t.tablespace_name
ORDER BY temp_mb DESC;
6
To kill a session, use:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;