Tracking Table Growth History in Oracle Databases (Single Instance & RAC)

This document provides methods to monitor and record table growth history in Oracle databases. It covers both single-instance Oracle and RAC (Real Application Clusters) deployments.

1. Using AWR for Table Growth (Diagnostics Pack Required)

Oracle’s Automatic Workload Repository (AWR) captures historical segment statistics.

Query for Single Instance:

SELECT o.owner, o.object_name, o.subobject_name, o.object_type,
       ss.snap_id, ss.bytes/1024/1024 AS size_mb,
       sn.begin_interval_time, sn.end_interval_time
FROM   dba_hist_seg_stat ss
JOIN   dba_hist_seg_stat_obj o
       ON ss.obj# = o.obj#
JOIN   dba_hist_snapshot sn
       ON sn.snap_id = ss.snap_id
WHERE  o.object_name = 'YOUR_TABLE_NAME'
AND    o.owner = 'YOUR_SCHEMA'
ORDER BY sn.begin_interval_time;

Query for RAC:

In RAC, AWR also tracks per-instance statistics. Add INSTANCE_NUMBER:

SELECT ss.instance_number,
       o.owner,
       o.object_name,
       sn.begin_interval_time,
       (ss.space_used_total * dt.block_size)/1024/1024 AS size_mb
FROM   dba_hist_seg_stat ss
JOIN   dba_hist_seg_stat_obj o
       ON ss.obj# = o.obj#
JOIN   dba_hist_snapshot sn
       ON ss.snap_id = sn.snap_id
      AND ss.dbid = sn.dbid
      AND ss.instance_number = sn.instance_number
JOIN   dba_tablespaces dt
       ON o.tablespace_name = dt.tablespace_name
WHERE  o.object_name = 'table_name'
AND    o.owner = 'table owner'
ORDER  BY sn.begin_interval_time, ss.instance_number;

INSTANCE_NUMBER shows which RAC instance reported stats.
Aggregate across instances for cluster-wide table growth.

2. Using DBA_SEGMENTS (Current Table Size)

This method works in both single and RAC databases. Segments exist at the database level, so the result is cluster-wide.

SELECT owner, segment_name, bytes/1024/1024 AS size_mb
FROM   dba_segments
WHERE  segment_name = 'YOUR_TABLE_NAME'
AND    owner = 'YOUR_SCHEMA';

3. Custom Table Growth History Logging (Recommended for Long-Term Tracking)

For databases without Diagnostics Pack, or when fine-grained control is needed, create a custom logging mechanism.

Step 1 – Create History Table

CREATE TABLE table_growth_history (
    snap_time      DATE,
    owner          VARCHAR2(30),
    table_name     VARCHAR2(30),
    size_mb        NUMBER,
    collected_from_instance VARCHAR2(10)
);

Step 2 – Insert Growth Snapshot

Single Instance:

INSERT INTO table_growth_history
SELECT SYSDATE,
       owner,
       segment_name,
       bytes/1024/1024,
       NULL
FROM   dba_segments
WHERE  segment_type = 'TABLE'
AND    owner = 'YOUR_SCHEMA';

RAC (with instance identification):

INSERT INTO table_growth_history
SELECT SYSDATE,
       owner,
       segment_name,
       bytes/1024/1024,
       SYS_CONTEXT('USERENV','INSTANCE_NAME')
FROM   dba_segments
WHERE  segment_type = 'TABLE'
AND    owner = 'YOUR_SCHEMA';

Step 3 – Schedule Collection

Use DBMS_SCHEDULER or DBMS_JOB to run the insert job daily/weekly for ongoing growth tracking.

4. Tablespace Growth (Optional)

To track tablespace rather than individual tables:

SELECT sn.begin_interval_time,
       ts.tablespace_name,
       (ts.tablespace_size*ts.block_size)/1024/1024 AS size_mb,
       (ts.tablespace_usedsize*ts.block_size)/1024/1024 AS used_mb
FROM   dba_hist_tablespace_stat ts,
       dba_hist_snapshot sn
WHERE  ts.snap_id = sn.snap_id
ORDER  BY sn.begin_interval_time;

5. Best Practices

Use AWR if Diagnostics Pack is licensed.
For non-licensed environments, implement custom logging with DBA_SEGMENTS.
In RAC, use INSTANCE_NUMBER for instance-wise breakdown, but track growth at the cluster level.
Retain growth history for capacity planning and proactive space management.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Tracking Table Growth History in Oracle Databases (Single Instance & RAC)