Scops
Docs Download

Oracle problems

The Database Is Growing Fast: What Grew, Since When, and the Fix

3 November 2026

A database that grows 200 GB in a month when it used to grow 20 has one of a few causes: a new table that logs everything, a purge that stopped, a LOB column that keeps every version, an index nobody needed, or a partition scheme with no end. The segments are in DBA_SEGMENTS and the tablespaces in DBA_TABLESPACE_USAGE_METRICS, so what is big is one query. Since when is the harder question, because Oracle keeps no growth history you may read without the Diagnostics Pack; the creation dates of objects and datafiles are the clues it leaves, and a monitoring platform that keeps the sizes itself is the answer. The queries below find the segment, the fix stops the inflow and gives the space back, and the forecast says when the disk runs out if nothing changes.

Where the space is

SELECT m.tablespace_name,
       ROUND(m.used_space*t.block_size/1024/1024/1024, 1) AS used_gb,
       ROUND(m.tablespace_size * t.block_size/1024/1024/1024, 1)
                                                           AS max_gb,
       ROUND(m.used_percent, 1)                            AS pct
FROM   dba_tablespace_usage_metrics m
JOIN   dba_tablespaces t ON t.tablespace_name = m.tablespace_name
ORDER  BY used_gb DESC;

Oracle's reference: USED_SPACE is the total space consumed by all objects created in the tablespace, and TABLESPACE_SIZE the maximum size of the tablespace, which, if any datafile has autoextend enabled, is the maximum size to which the tablespace can grow with the underlying storage free space taken into account. USED_PERCENT is therefore the honest figure: used against everything the tablespace could ever get, disk included.

Which segments are big

SELECT owner, segment_name, segment_type, tablespace_name,
       ROUND(bytes/1024/1024/1024, 1) AS gb, extents
FROM   dba_segments
WHERE  bytes > 1024*1024*1024
ORDER  BY bytes DESC
FETCH  FIRST 30 ROWS ONLY;

DBA_SEGMENTS describes the storage allocated for all segments in the database, with BYTES the size of each. A LOBSEGMENT larger than the table it belongs to is a LOB column keeping old versions or storing what should be a file; an INDEX as large as its table is a wide key; a TABLE PARTITION list with one partition far bigger than the rest is the partition that collects everything new.

Which of them are new

SELECT o.owner, o.object_name, o.object_type, o.created,
       ROUND(SUM(s.bytes)/1024/1024) AS mb
FROM   dba_objects o
JOIN   dba_segments s
       ON s.owner = o.owner AND s.segment_name = o.object_name
WHERE  o.created > SYSDATE - 30
AND    o.object_type IN ('TABLE', 'INDEX', 'LOB')
GROUP  BY o.owner, o.object_name, o.object_type, o.created
ORDER  BY mb DESC
FETCH  FIRST 30 ROWS ONLY;

A table created three weeks ago that already holds 80 GB is the month's growth, and its name usually says what it is: a log, an audit trail, a staging table that was never truncated, a copy made for a migration. For the rest, the datafiles say when the growth happened, because every one of them was added on a date:

SELECT TO_CHAR(creation_time, 'YYYY-MM') AS month, COUNT(*) AS files,
       ROUND(SUM(bytes)/1024/1024/1024) AS gb
FROM   v$datafile
WHERE  creation_time > ADD_MONTHS(SYSDATE, -12)
GROUP  BY TO_CHAR(creation_time, 'YYYY-MM')
ORDER  BY 1;

The history Oracle does not give you

Oracle records tablespace sizes over time in DBA_HIST_TBSPC_SPACE_USAGE, and the licensing guide puts every DBA_HIST_ view, with a handful of exceptions that do not include this one, in the Diagnostics Pack, which is sold for Enterprise Edition only. On Standard Edition 2, or on an Enterprise Edition without the pack, the database has no readable record of how big a table was last month. Scops keeps that record itself: the size of every tablespace, datafile and large segment, every day, on every edition, which is what turns "the database grew" into "this table grew 4 GB a day since the 14th".

The fix, in order

  1. Name the segment and the reason. The three queries give the name; the reason is in the application: a log level left at debug, a retry loop that writes a row per attempt, an audit nobody reads, a purge job that stopped, a LOB that stores files, a partition range without a next partition.
  2. Stop the inflow where it is a defect. The debug log, the stuck purge and the staging table that is never truncated are fixed at the source, and the growth stops that day.
  3. Move the old rows out. The tables that grow are the ones with history nobody queries. Archiving moves old rows to an archive database online, in rowid chunks, and keeps them queryable, so the production tables hold the data that is used and the growth curve bends.
  4. Compress what stays. Oracle's table and index compression cut the size of cold data several times over; the Compression module manages it from one screen, with the saving measured before it is applied.
  5. Give the space back. A DELETE leaves the segment the size it was, because the high-water mark does not move. ALTER TABLE ... SHRINK SPACE, with row movement enabled, or ALTER TABLE ... MOVE and an index rebuild return it, and ALTER DATABASE DATAFILE ... RESIZE returns it to the disk.
  6. Forecast, then decide. With the sizes kept daily, the day the tablespace, the ASM disk group or the datafile limit runs out is a date, not a feeling, and the space is bought, or the rows archived, before it.

See it coming

Radar Engine, the detection layer of Scops, measures the size of every database daily, learns its usual growth, and forecasts every tablespace, the datafiles and ASM to the day they fill, so a month that grows ten times the usual is a finding in its first week, with the segments that did it:

DetectorClockWhen it fires
Unusual database growthDaily. Growth far above the usual, or a lasting change.Watch. If the space limit comes within 90 days: Warning.
Tablespace filling upHourly forecast, above 30% of the maximum size, after three days of readings.The date it fills, against horizons of 14, 45 and 90 days.
Datafile space running outDaily forecast: datafiles to their maximum size, then ASM.The date when ASM is full, against horizons of 14, 45 and 90 days. Else Watch.

The finding names the database, the growth against the usual, and the segments that grew most. Scops reads the sizes through a read-only account, with nothing installed in the database and no Diagnostics Pack, on Standard Edition 2 as on Enterprise Edition.

Questions

How do I find what is growing in my Oracle database?
DBA_SEGMENTS ordered by BYTES names the largest segments, DBA_OBJECTS joined to it names the ones created recently, and DBA_TABLESPACE_USAGE_METRICS shows each tablespace against the most it can grow. The growth itself needs a size history, which Oracle keeps only in the Diagnostics Pack views and Scops keeps on every edition.

Does Oracle keep a history of database size?
In DBA_HIST_TBSPC_SPACE_USAGE, which belongs to the Diagnostics Pack and so to Enterprise Edition with the pack. Without it, the creation dates of objects and datafiles are the only dates, and a monitoring platform that records the sizes daily is the history.

Why does the database not get smaller after a big DELETE?
Because a segment keeps its allocated size; the high-water mark does not move down on delete. ALTER TABLE ... SHRINK SPACE or ALTER TABLE ... MOVE with an index rebuild returns the space to the tablespace, and a datafile resize returns it to the disk.

What is the fastest way to stop the growth?
Fix the defect that produces the rows, then archive the old rows out of the growing tables and compress what stays. Archiving online, in rowid chunks, and Oracle compression are the two Scops modules built for exactly this.

Related problems

Sources