Oracle tablespace full (ORA-01653 / ORA-01652): a runbook

#Know which error you have

#1. Real usage, including autoextend headroom

SELECT tablespace_name,
       ROUND(used_space * ts.block_size / 1024 / 1024 / 1024, 1)       AS used_gb,
       ROUND(tablespace_size * ts.block_size / 1024 / 1024 / 1024, 1)  AS max_gb,
       ROUND(used_percent, 1)                                          AS used_pct
FROM   dba_tablespace_usage_metrics m
JOIN   dba_tablespaces ts USING (tablespace_name)
ORDER  BY used_percent DESC;

dba_tablespace_usage_metrics calculates the percentage against MAXSIZE for autoextend files, which is the number that matters. A tablespace can look 99% full by allocated size and still have plenty of room.

SELECT file_name, ROUND(bytes/1024/1024/1024,1) gb, autoextensible, ROUND(maxbytes/1024/1024/1024,1) max_gb
FROM   dba_data_files WHERE tablespace_name = 'USERS';

#2. Add space

Before you add space, check the filesystem or ASM disk group has free space. Autoextend to 32 GB doesn't help if the disk group has 5 GB left.

SELECT name, ROUND(free_mb/1024) free_gb, ROUND(total_mb/1024) total_gb FROM v$asm_diskgroup;

Then choose one of these:

-- Resize an existing file
ALTER DATABASE DATAFILE '+DATA/ORCL/DATAFILE/users.275.1123456789' RESIZE 30G;

-- Turn on or raise autoextend
ALTER DATABASE DATAFILE '+DATA/ORCL/DATAFILE/users.275.1123456789' AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

-- Add a datafile (smallfile tablespaces max out at about 32 GB per file with an 8 KB block size)
ALTER TABLESPACE users ADD DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

Warning

In a Data Guard setup with STANDBY_FILE_MANAGEMENT=AUTO, new datafiles are created on the standby automatically. Check that the standby has the same free space, or recovery stops there.

#3. TEMP and UNDO

-- Who is using TEMP right now?
SELECT s.sid, s.username, s.sql_id, ROUND(u.blocks * t.block_size / 1024 / 1024) AS mb
FROM   v$tempseg_usage u JOIN v$session s ON s.saddr = u.session_addr
JOIN   dba_tablespaces t ON t.tablespace_name = u.tablespace
ORDER  BY mb DESC FETCH FIRST 10 ROWS ONLY;

ALTER TABLESPACE temp ADD TEMPFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

For TEMP, adding space is often treating the symptom. A query that spills 200 GB to TEMP usually has a bad plan. Look at the sql_id.

For UNDO, find the large or long transaction in v$transaction (used_ublk) joined to v$session, and talk to its owner before you kill it. Rolling back takes as long as the transaction ran.

#4. What grew?

SELECT owner, segment_name, segment_type, ROUND(bytes/1024/1024/1024, 2) gb
FROM   dba_segments WHERE tablespace_name = 'USERS'
ORDER  BY bytes DESC FETCH FIRST 15 ROWS ONLY;

For historical growth, AWR keeps it in dba_hist_tbspc_space_usage.

#5. Reclaim space (not during an incident)

PURGE DBA_RECYCLEBIN;                                -- dropped objects still hold space
ALTER TABLE app.audit_log ENABLE ROW MOVEMENT;
ALTER TABLE app.audit_log SHRINK SPACE CASCADE;      -- online, needs ASSM

#Prevent it next time

• Alert at 85% of MAXSIZE using dba_tablespace_usage_metrics, not on allocated size.
• Use bigfile tablespaces for new large tablespaces, so there's one file to manage.
• Add purge and archiving jobs for audit and log tables when you create them, not after the first outage.