$ guides / oracle-database / oracle-database-autoextend-maxsize-reached ▌

Operations Guides

Oracle autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem

ORA-01653 (table) or ORA-01654 (index) fires on a production instance. Application writes fail. You log in, check df on the datafile filesystem, and see hundreds of gigabytes free. ASM disk group shows plenty of headroom. There is no obvious space problem at the storage layer. Yet Oracle insists the tablespace cannot extend.

This is the autoextend ceiling gotcha. The datafile has AUTOEXTEND ON, but it has reached its MAXSIZE. Oracle refuses to grow the file further even though the disk beneath has abundant space. Disk-only monitoring never sees this coming because the disk is not the constraint. The per-datafile MAXSIZE is.

The fix is usually one of two operations: raise MAXSIZE on the existing datafile, or add another datafile to the tablespace. The harder problem is noticing in the first place.

What this means

Each Oracle datafile has its own MAXSIZE, independent of the filesystem or ASM disk group it lives on. AUTOEXTEND ON tells Oracle to grow the file when more space is needed, but only up to MAXSIZE. Once BYTES equals MAXBYTES in DBA_DATA_FILES, the file cannot extend any further. New extent allocations in that tablespace fail with ORA-01653 (unable to extend table) or ORA-01654 (unable to extend index), even though storage has plenty of room.

There are two flavors of this failure.

An explicit MAXSIZE that was set too low when the datafile was created. Often a leftover from provisioning scripts that hard-coded MAXSIZE 10G or MAXSIZE 20G and were never revisited as the workload grew.

The implicit smallfile tablespace ceiling. Oracle documents each smallfile datafile as holding up to approximately 4 million blocks (2^22); the physical database limits table says the platform-dependent per-datafile maximum is typically 2^22 - 1 blocks. At the default 8KB block size, that is roughly 32GB. Even with MAXSIZE UNLIMITED, a smallfile datafile stops extending at that physical limit. UNLIMITED means “no DBA-imposed ceiling,” not “no ceiling at all.”

Bigfile tablespaces raise the per-file ceiling dramatically (terabytes rather than gigabytes for the same block size). But the same gotcha applies, and it is arguably worse: a bigfile tablespace has exactly one datafile. If it hits MAXSIZE, you cannot add a second datafile. You must resize or raise MAXSIZE on that single file.

The other half of the trap is monitoring. A naive tablespace-space query against DBA_FREE_SPACE shows current allocation and free space within the datafile, but ignores the autoextend ceiling. A tablespace can look healthy by that measure and still be one INSERT away from ORA-01653. The utilization formula must use max capacity (including autoextend MAXSIZE) as the denominator, not current allocation size, otherwise autoextend headroom is invisible.

flowchart TD
  A["ORA-01653 / 01654 fired"] --> B["Check df / ASM"]
  B --> C{"Filesystem has free space?"}
  C -- Yes --> D["Disk-only monitoring is blind"]
  C -- No --> E["Real storage exhaustion"]
  D --> F["Query DBA_DATA_FILES"]
  F --> G{"BYTES >= MAXBYTES?"}
  G -- Yes --> H["Autoextend ceiling hit"]
  G -- No --> I["Check quota / extent / segment"]
  H --> J["Raise MAXSIZE or add datafile"]

Common causes

Cause	What it looks like	First thing to check
Explicit MAXSIZE set too small	`BYTES` equals `MAXBYTES` on one datafile, df shows plenty of free space	`DBA_DATA_FILES.MAXBYTES` vs `BYTES`
Smallfile physical ceiling	Many datafiles stalled near 32GB on 8KB block size	Count of datafiles where `BYTES >= MAXBYTES - 1MB`
Bigfile at MAXSIZE	Single-datafile tablespace, `BYTES` equals `MAXBYTES`, cannot add a file	`DBA_TABLESPACES.BIGFILE`
MAXSIZE UNLIMITED misread	UNLIMITED set, but error still fires	Confirm smallfile vs bigfile type
Misleading `DBA_FREE_SPACE` query	Query showed space, but ignored autoextend headroom	Use `DBA_TABLESPACE_USAGE_METRICS` or the formula below

Quick checks

All read-only. Safe to run on production.

-- Show MAXSIZE vs current size per datafile, smallest headroom first
SELECT TABLESPACE_NAME, FILE_NAME,
       ROUND(BYTES/1048576, 1) AS current_mb,
       ROUND(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)/1048576, 1) AS max_mb,
       AUTOEXTENSIBLE,
       ROUND((DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES) - BYTES)/1048576, 1) AS headroom_mb
FROM DBA_DATA_FILES
ORDER BY headroom_mb ASC;

-- Tablespace utilization against MAX capacity (autoextend-aware)
SELECT df.TABLESPACE_NAME,
       ROUND((df.TOTAL_MB - NVL(fs.FREE_MB, 0)) / df.MAX_MB * 100, 2) AS used_pct_of_max,
       ROUND(df.TOTAL_MB, 2) AS current_mb,
       ROUND(df.MAX_MB, 2) AS max_mb,
       ROUND(NVL(fs.FREE_MB, 0) + (df.MAX_MB - df.TOTAL_MB), 2) AS effective_free_mb
FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/1048576 AS TOTAL_MB,
             SUM(DECODE(AUTOEXTENSIBLE,'YES',MAXBYTES,BYTES))/1048576 AS MAX_MB
      FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) df
LEFT JOIN (SELECT TABLESPACE_NAME, SUM(BYTES)/1048576 AS FREE_MB
           FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) fs
ON df.TABLESPACE_NAME = fs.TABLESPACE_NAME
ORDER BY used_pct_of_max DESC;

-- 11gR2+ shortcut view (accounts for autoextend internally)
SELECT TABLESPACE_NAME, ROUND(USED_PERCENT, 2) AS used_pct
FROM DBA_TABLESPACE_USAGE_METRICS
ORDER BY USED_PERCENT DESC;

# Confirm underlying storage is not the actual constraint
df -h /u01/oradata
# For ASM disk groups:
# asmcmd lsdg

-- Catch ORA-01653 / ORA-01654 in the queryable alert log (12c+)
SELECT originating_timestamp, message_text
FROM V$DIAG_ALERT_EXT
WHERE message_text LIKE '%ORA-01653%' OR message_text LIKE '%ORA-01654%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;  -- 12c+ syntax. For 11g use ROWNUM <= 20

-- Identify the largest segments in the stuck tablespace
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE,
       ROUND(SUM(BYTES)/1048576, 1) AS mb
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = '&stuck_tablespace'
GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
ORDER BY mb DESC
FETCH FIRST 10 ROWS ONLY;  -- 12c+ syntax. For 11g use ROWNUM <= 10

How to diagnose it

Confirm ORA-01653 or ORA-01654 is actually firing. Check the application error, the alert log, or V$DIAG_ALERT_EXT. Note the tablespace name in the error text.
Run the first quick check against that tablespace. If headroom_mb is zero or near zero on the datafiles in that tablespace, the autoextend ceiling is the problem.
Run the df or asmcmd lsdg check on the filesystem or ASM disk group hosting the datafile. If free space is abundant there, the storage layer is not the constraint.
Cross-check with the autoextend-aware utilization query. used_pct_of_max should be near 100% if the ceiling is hit. If it is well below 100%, the problem is elsewhere: quota, segment extent allocation failure for a different reason, or a single large extent request that does not fit in the largest free extent even though total free space looks fine.
Verify smallfile vs bigfile: SELECT TABLESPACE_NAME, BIGFILE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = '<name>';. A bigfile tablespace has only one datafile, which restricts your fix options.
Check whether MAXSIZE was explicit or defaulted. Compare MAXBYTES to the smallfile ceiling for your block size. A MAXBYTES of exactly 32GB on an 8KB block smallfile tablespace is the default physical ceiling, not necessarily an explicit DBA choice.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`DBA_DATA_FILES.BYTES` vs `MAXBYTES`	Distance to the per-file ceiling	Headroom shrinking week over week
`DBA_TABLESPACE_USAGE_METRICS.USED_PERCENT`	Autoextend-aware utilization at the tablespace level	Sustained above 85%
Filesystem or ASM free space	The actual underlying storage constraint	Necessary baseline, but not sufficient alone
ORA-01653 / ORA-01654 in alert log	The hard error	Any occurrence in production
Tablespace growth rate	Days-to-ceiling projection	Growth rate trending up
Datafile count per tablespace	Whether you have flexibility to add a file	Single-file bigfile tablespace is a single point of failure

Severity guidance: PAGE when any tablespace is above 95% of max capacity with no autoextend headroom and no underlying filesystem or ASM headroom, or when ORA-01653/ORA-01654 errors are actively occurring. TICKET above 85%. PLAN above 75%.

Fixes

Two safe options, plus one structural change.

Raise MAXSIZE on the existing datafile

The fastest fix when the underlying filesystem or ASM disk group has free space.

-- Online, non-blocking. File remains available.
ALTER DATABASE DATAFILE '/u01/oradata/mydb/users01.dbf'
  AUTOEXTEND ON NEXT 100M MAXSIZE 50G;

Verify with the first quick check afterward: BYTES should still equal the old size (no immediate growth), but MAXBYTES should now reflect the new ceiling.

Caveats:

The new MAXSIZE must be at or below the physical block limit for the datafile type and the operating-system file-size limit. For an autoextendable file whose MAXSIZE exceeds the allowed block count, Oracle raises ORA-03206: “maximum file size of (string) blocks in AUTOEXTEND clause is out of range.” At 8KB blocks, the smallfile limit is about 32GB; exact platform limits can differ.
On a bigfile tablespace the same command applies, with a much higher ceiling.
Raising MAXSIZE does not consume storage immediately. It only changes the upper bound.

Add a datafile to the tablespace

The right fix when the existing datafile is near the smallfile physical limit, or when you want to spread I/O across multiple files.

-- Add a second datafile to a smallfile tablespace
ALTER TABLESPACE users
  ADD DATAFILE '/u02/oradata/mydb/users02.dbf'
  SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

For bigfile tablespaces you cannot add a second datafile. You must raise MAXSIZE or resize the single existing file.

Caveats:

Place the new file on storage that is at least as fast as the original. A new datafile on slower storage introduces latency variance.
On raw ASM, prefer ASM-managed file names ('+DATA') unless you have a naming convention.

Migrate smallfile to bigfile (longer term)

For tablespaces that consistently bump the smallfile ceiling, consider migrating to bigfile. This is a structural change with its own tradeoffs: one file per tablespace, different backup and recovery characteristics, and a single point of failure if that file is lost or corrupted. Plan it for a maintenance window, not for the middle of an incident.

Prevention

The monitoring fix is the most important part of prevention. Disk-only alerts miss this failure mode by definition.

Use autoextend-aware utilization as the alerting metric. DBA_TABLESPACE_USAGE_METRICS.USED_PERCENT accounts for MAXSIZE. If you compute manually, the denominator must be MAXBYTES, not BYTES. This is the single biggest monitoring fix you can make.
Alert on per-datafile headroom, not just tablespace aggregates. A tablespace with three datafiles where one is at its ceiling and two have headroom will still produce ORA-01653 on segments allocated in the full file. Alert when any datafile has less than a defined threshold of MAXSIZE headroom.
Project days to ceiling. Tablespace storage degrades cliff-edge: writes succeed at 99% and fail at 100%. Track growth rate and forecast when each datafile hits MAXSIZE. That gives you runway to plan a datafile add during business hours rather than at 3am.
Make MAXSIZE choices explicit and documented. A MAXSIZE 10G left over from provisioning on a 2TB volume is a future incident. Either set MAXSIZE to match the storage actually allocated to this database, or remove the artificial ceiling (with awareness of the smallfile physical limit).

How Netdata helps

Tablespace utilization is collected per second using the autoextend-aware percentage, so a half-empty filesystem with a stalled datafile is visible before ORA-01653 fires.
Per-datafile MAXSIZE headroom trends let you alert on shrinking ceiling distance rather than waiting for the cliff-edge error.
Correlating tablespace growth with redo generation rate, transactions per second, and physical I/O helps distinguish real load increases from a runaway INSERT or an audit trail that started growing unexpectedly.
Alert log scraping surfaces ORA-01653 and ORA-01654 entries as they appear, with the tablespace name preserved for fast triage.
Anomaly detection on growth rate catches the step-change pattern that precedes most space incidents: a new batch job, a looping process, or a configuration change that suddenly increased data volume.
Filesystem and ASM disk group free space can be shown alongside the database-level utilization in the same dashboard, making the “half-empty filesystem” pattern immediately visible.

See Oracle Database monitoring with Netdata for per-second metrics, alerting, and anomaly detection on these signals.

The Netdata solution

Oracle Database monitoring with Netdata

Netdata monitors Oracle Database with per-second metrics and automatic dashboards. Watch wait events, redo and archive-log activity, tablespace and undo space, and session and lock activity so the failure modes in these runbooks surface before the instance hangs.

See Oracle monitoring → Start monitoring free