Scops
Docs Download

Oracle problems

Oracle Tablespace Full: ORA-01653, the Usage Query That Counts, and the Date It Fills

2 November 2026

ORA-01653 is the error that monitoring was supposed to prevent, and the reason it did not is usually the query: it measured the files, not the room. A tablespace at 95% of a file that can grow to 32 GB on a disk with 2 TB free is fine. One at 60% on a disk with 3 GB free is tonight's page.

What the errors say

Each names the segment that wanted the space, which is rarely the one that took it. ORA-01653 on a small table means the big one next to it filled the tablespace.

The usage query that counts

Three numbers, not one: what the files hold now, what they may grow to, and what the disk allows.

SELECT tablespace_name,
       ROUND(size_gb, 1)              AS size_gb,
       ROUND(max_gb, 1)               AS max_gb,
       ROUND(used_gb, 1)              AS used_gb,
       ROUND(used_gb / size_gb * 100) AS pct_of_files,
       ROUND(used_gb / max_gb * 100)  AS pct_of_max,
       ROUND(max_gb - used_gb, 1)     AS room_gb
FROM  (SELECT t.tablespace_name,
              f.cur_gb                      AS size_gb,
              f.max_gb,
              f.cur_gb - NVL(fs.free_gb, 0) AS used_gb
       FROM   dba_tablespaces t
       JOIN  (SELECT tablespace_name,
                     SUM(bytes) / POWER(1024, 3) AS cur_gb,
                     SUM(CASE WHEN autoextensible = 'YES'
                              THEN GREATEST(maxbytes, bytes)
                              ELSE bytes END)
                       / POWER(1024, 3)          AS max_gb
              FROM   dba_data_files
              GROUP  BY tablespace_name) f
              ON f.tablespace_name = t.tablespace_name
       LEFT JOIN (SELECT tablespace_name,
                         SUM(bytes) / POWER(1024, 3) AS free_gb
                  FROM   dba_free_space
                  GROUP  BY tablespace_name) fs
              ON fs.tablespace_name = t.tablespace_name
       WHERE  t.contents = 'PERMANENT')
ORDER  BY pct_of_max DESC;

pct_of_files is the number most tools show, and it alarms on every autoextend tablespace that does its job. pct_of_max and room_gb are what the error depends on. GREATEST is there because a file resized by hand past its maximum has MAXBYTES below BYTES.

Then the disk, because a maximum size is a promise the storage has to keep:

SELECT name, state, type,
       ROUND(total_mb       / 1024) AS total_gb,
       ROUND(free_mb        / 1024) AS free_gb,
       ROUND(usable_file_mb / 1024) AS usable_gb
FROM   v$asm_diskgroup;

USABLE_FILE_MB is the number that counts on ASM: free space after the redundancy, less the reserve to rebuild a failed disk. On a file system, df -h on each mount point of DBA_DATA_FILES, and remember that the archived logs may share it.

What took the space

SELECT owner, segment_name, segment_type, partition_name,
       ROUND(bytes / POWER(1024, 3), 1) AS gb
FROM   dba_segments
WHERE  tablespace_name = 'DATA'
ORDER  BY bytes DESC
FETCH FIRST 15 ROWS ONLY;

That is a photograph. What you need is the film: which segment grew this week. Oracle keeps that in DBA_HIST_SEG_STAT and DBA_HIST_TBSPC_SPACE_USAGE, both part of the Diagnostics Pack, an extra license on Enterprise Edition and not available on Standard Edition. Without it, sample DBA_SEGMENTS into a table of your own once a day, the way this article does for sessions. Scops records the size of every segment each day, Diagnostics Pack or not, and its Sizing page shows what grew and what was freed in any window.

Fix it, in this order

  1. Give the file room, if the disk has it. Both statements run online: 
    ALTER DATABASE DATAFILE '/u02/oradata/ORCL/data01.dbf'
      AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

ALTER TABLESPACE data
      ADD DATAFILE '/u02/oradata/ORCL/data02.dbf' SIZE 10G
      AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;
    A smallfile datafile with an 8 KB block stops at 4,194,303 blocks, just under 32 GB, so a large tablespace grows by files. A bigfile tablespace has one file that can reach 32 TB.
  2. Mind db_files. Every datafile counts against it. At the limit, ADD DATAFILE fails with ORA-00059, and a change of db_files needs a restart. SELECT COUNT(*) FROM v$datafile; against the parameter tells you how close you are.
  3. Take back what nobody needs. PURGE DBA_RECYCLEBIN; for dropped tables that still hold their space. Old partitions: drop them, archive the rows, or compress them by age.
  4. Find the writer when one segment grows far faster than the business does: a log table nobody purges, an audit trail, a queue that is never dequeued. The fix there is a purge job, not a disk.

See it coming: the date, not the percent

A tablespace that fills in three months looks the same, today, as one that will never fill. The difference is the slope, and a slope needs history. The capacity detectors of Radar Engine, the detection layer of Scops, are countdowns:

DetectorClockWhen it fires
Tablespace filling upHourly forecast, above 30% of the maximum size, after three days of readingsThe date it fills, against horizons of 14, 45 and 90 days. 90% and not growing for 24 hours: Watch. 90% with 5 GB or less left: at least Warning.
Tablespace filling fastLive, every 5 minutes: 70% or more, with steady growth for 90 minutesFull within 1 hour: Critical. Within 6 hours: Warning. Else, or the usual night load: Watch.
Datafile space running outDaily forecast: the datafiles to their maximum size, then ASMThe date when ASM is full, against the same horizons.
ASM disk group running lowHourly forecast and levelUnder 5%: Watch. Under 2% and 100 GB: Warning. Under 1% headroom and 50 GB usable: Critical.
Datafiles near the limitHourly, against db_files85%: Watch. Full within 30 days: Warning. Under 3 left at 98%, or full within 2 days: Critical.
Unusual database growthDailyGrowth far above the usual: Watch. If the space limit comes within 90 days: Warning.

One step further: with Automatic datafiles on, the Tablespace Usage alert of Scops adds a datafile, or grows a bigfile tablespace, when a tablespace on ASM crosses the level of the rule, up to a daily limit. The alert opens only when the add fails.

Questions

Why does my tablespace show 99% used but inserts still work?
Autoextend. The files grow until their maximum size or the disk stops them. Measure against the maximum size and the disk, as in the query above, not against the current file size.

Does adding a datafile need downtime?
No. ADD DATAFILE, RESIZE and AUTOEXTEND run online. A restart is needed only when db_files is at its limit.

Should I use bigfile tablespaces?
For large tablespaces on ASM, usually yes: one file up to 32 TB with 8 KB blocks and no pressure on db_files. Let RMAN back it up in sections, or one file becomes one long channel.

How do I know which table filled the tablespace?
Without history, DBA_SEGMENTS shows the largest segments, not the fastest growing. DBA_HIST_SEG_STAT shows the growth but needs the Diagnostics Pack. Scops records every segment daily and lists what grew in any window on its Sizing page.

Related problems

Sources