DBA_HIST_SEG_STAT: SQL Queries to Track Oracle Growth

Table of Contents

Introduction

Have you ever needed to analyze Oracle database growth trends but found yourself limited by current snapshot data? In real production environments, tracking database growth trends is a critical aspect of DBA life because this allows businesses not only to foresee the real position in terms of existing hardware but also enables planning future expenses for hardware and storage.

DBA_HIST_SEG_STAT is Oracle’s most powerful view for analyzing database growth trends and historical segment statistics. This comprehensive guide will show you exactly how to leverage this view for capacity planning and growth trend analysis.

Quick Reference: Essential DBA_HIST_SEG_STAT Queries

Need to check growth immediately? Use these queries:

Top 10 Growing Segments (Last 7 Days)

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A40
COLUMN growth_mb FORMAT 999,990.99

SELECT 
    o.owner,
    o.object_name,
    ROUND((end_size - start_size)/1024/1024, 2) AS growth_mb
FROM (
    SELECT 
        obj#,
        MIN(space_allocated_total) AS start_size,
        MAX(space_allocated_total) AS end_size
    FROM 
        dba_hist_seg_stat
    WHERE 
        snap_id BETWEEN (SELECT MAX(snap_id)-168 FROM dba_hist_snapshot)
                    AND (SELECT MAX(snap_id) FROM dba_hist_snapshot)
    GROUP BY 
        obj#
    HAVING 
        MAX(space_allocated_total) > MIN(space_allocated_total)
) growth
JOIN dba_objects o ON growth.obj# = o.object_id
ORDER BY 
    (end_size - start_size) DESC
FETCH FIRST 10 ROWS ONLY;

Tablespace Growth Trend (Last 30 Days)

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN tablespace_name FORMAT A30
COLUMN growth_gb FORMAT 999,990.99

SELECT 
    ts.name AS tablespace_name,
    ROUND((MAX(h.space_allocated_total) - MIN(h.space_allocated_total))/1024/1024/1024, 2) AS growth_gb
FROM 
    dba_hist_seg_stat h
    JOIN v$tablespace ts ON h.ts# = ts.ts#
    JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
WHERE 
    s.begin_interval_time >= SYSDATE - 30
GROUP BY 
    ts.name
HAVING 
    MAX(h.space_allocated_total) > MIN(h.space_allocated_total)
ORDER BY 
    growth_gb DESC;

Daily Growth Rate

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN snap_date FORMAT A12
COLUMN daily_growth_mb FORMAT 999,990.99

SELECT 
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD') AS snap_date,
    ROUND(SUM(h.space_allocated_delta)/1024/1024, 2) AS daily_growth_mb
FROM 
    dba_hist_seg_stat h
    JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
WHERE 
    s.begin_interval_time >= SYSDATE - 7
GROUP BY 
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD')
ORDER BY 
    snap_date;

Jump to section:

Why Use DBA_HIST_SEG_STAT for Growth Tracking?

Understanding database growth trends is essential for several critical business functions:

Capacity Planning: Predict when you’ll need additional storage based on historical patterns

Budget Forecasting: Plan hardware purchases and cloud scaling costs effectively

Performance Optimization: Identify objects with rapid growth that may impact performance

Compliance Management: Meet regulatory requirements for data retention and storage planning

Understanding DBA_HIST_SEG_STAT Columns

DBA_HIST_SEG_STAT captures comprehensive information about segments in historical form, including:

Space allocation and utilization (our primary focus for growth analysis)

SPACE_ALLOCATED_TOTAL: Total space allocated to the segment
SPACE_ALLOCATED_DELTA: Change in allocated space since last snapshot
SPACE_USED_TOTAL: Space actually used by data
SPACE_USED_DELTA: Change in used space since last snapshot

Logical reads and physical reads (performance metrics)

LOGICAL_READS_TOTAL: Total logical reads
LOGICAL_READS_DELTA: Change in logical reads
PHYSICAL_READS_TOTAL: Total physical reads
PHYSICAL_READS_DELTA: Change in physical reads

Key identifier columns:

SNAP_ID: Snapshot identifier (join with DBA_HIST_SNAPSHOT)
DBID: Database identifier
INSTANCE_NUMBER: Instance number in RAC environment
OWNER: Schema owner of the segment
OBJECT_NAME: Name of the database object
SUBOBJECT_NAME: Partition name (if applicable)
TABLESPACE_NAME: Tablespace containing the segment
OBJ#: Object number
DATAOBJ#: Data object number
TS#: Tablespace number

DBA_HIST_SEG_STAT vs Current Views

Historical Data Retention

When this historical view is combined with DBA_HIST_SNAPSHOT, we get the required information in comparative form for tracking data growth rates. The historical information captured will remain available in the database only for those dates and snapshots configured by the DBA to be kept in the database.

Example: If the DBA has configured 15 days of snapshots to be retained, we can retrieve comparative information for the last 15 days only.

Long-Term Tracking Strategy

If you want comparison reports for a year or more, you should follow alternate approaches:

Create dedicated tables populated by scheduled jobs
Export and archive AWR data regularly
Use Oracle Enterprise Manager for extended retention

Oracle Views for Growth Analysis

To generate data growth reports, Oracle provides various means which we’ll explore here. We can track growth for objects (tables, indexes), tablespaces, schemas, or even at database level using the views provided by Oracle.

Important views for growth tracking:

View Name	Type	Use Case
DBA_HIST_SEG_STAT	Historical	Long-term segment growth analysis
DBA_SEGMENTS	Current	Current segment sizes
DBA_TABLESPACES	Current	Current tablespace usage
DBA_HIST_TBSPC_SPACE_USAGE	Historical	Tablespace growth over time
DBA_HIST_SNAPSHOT	Historical	Timestamp correlation

Key Concept: Views which do not have HIST in their name provide “accumulated till date” information. This means you can derive current size of an object using DBA_SEGMENTS and DBA_TABLESPACES. These views store data usage information in terms of BYTES.

Understanding Space Allocation

To explain this, when we create a table (with segment allocation immediate option), Oracle allocates space for this object which is not utilized until we add a row. In simple terms, used space is the “High Water Mark” where Oracle has used the allocated space “up to”.

DBA_HIST_SEG_STAT Practical Query Examples

Query 1: Object Growth Over Last 30 Days

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A40
COLUMN subobject_name FORMAT A30
COLUMN start_size_mb FORMAT 999,990.99
COLUMN end_size_mb FORMAT 999,990.99
COLUMN growth_mb FORMAT 999,990.99
COLUMN growth_pct FORMAT 990.99

SELECT 
    o.owner,
    o.object_name,
    o.subobject_name,
    ROUND(g.start_size/1024/1024, 2) AS start_size_mb,
    ROUND(g.end_size/1024/1024, 2) AS end_size_mb,
    ROUND((g.end_size - g.start_size)/1024/1024, 2) AS growth_mb,
    ROUND(((g.end_size - g.start_size)/NULLIF(g.start_size, 0)) * 100, 2) AS growth_pct
FROM (
    SELECT 
        obj#,
        dataobj#,
        MIN(space_allocated_total) AS start_size,
        MAX(space_allocated_total) AS end_size
    FROM 
        dba_hist_seg_stat h
        JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
    WHERE 
        s.begin_interval_time >= SYSDATE - 30
    GROUP BY 
        obj#, dataobj#
    HAVING 
        MAX(space_allocated_total) > MIN(space_allocated_total)
) g
JOIN dba_objects o ON g.obj# = o.object_id AND (g.dataobj# = o.data_object_id OR o.data_object_id IS NULL)
ORDER BY 
    (g.end_size - g.start_size) DESC
FETCH FIRST 20 ROWS ONLY;

What this shows:

Objects that grew the most in last 30 days
Both absolute growth (MB) and percentage growth
Helps identify rapidly growing tables/indexes

Query 2: Tablespace Growth Trend Analysis

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN tablespace_name FORMAT A30
COLUMN snap_date FORMAT A12
COLUMN size_gb FORMAT 999,990.99
COLUMN growth_gb FORMAT 999,990.99

SELECT 
    tablespace_name,
    TO_CHAR(snap_date, 'YYYY-MM-DD') AS snap_date,
    ROUND(total_size/1024/1024/1024, 2) AS size_gb,
    ROUND((total_size - LAG(total_size) OVER (PARTITION BY tablespace_name ORDER BY snap_date))/1024/1024/1024, 2) AS growth_gb
FROM (
    SELECT 
        ts.name AS tablespace_name,
        TRUNC(s.begin_interval_time) AS snap_date,
        SUM(h.space_allocated_total) AS total_size
    FROM 
        dba_hist_seg_stat h
        JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
        JOIN v$tablespace ts ON h.ts# = ts.ts#
    WHERE 
        s.begin_interval_time >= SYSDATE - 30
    GROUP BY 
        ts.name,
        TRUNC(s.begin_interval_time)
)
ORDER BY 
    tablespace_name, snap_date;

What this shows:

Daily tablespace growth patterns
Growth trends over 30 days
Helps predict when tablespace will need expansion

Query 3: Schema-Level Growth Summary

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN owner FORMAT A30
COLUMN total_objects FORMAT 999,999
COLUMN total_size_gb FORMAT 999,990.99
COLUMN growth_gb FORMAT 999,990.99
COLUMN avg_daily_growth_mb FORMAT 999,990.99

SELECT 
    owner,
    COUNT(DISTINCT obj#) AS total_objects,
    ROUND(MAX(total_size)/1024/1024/1024, 2) AS total_size_gb,
    ROUND((MAX(total_size) - MIN(total_size))/1024/1024/1024, 2) AS growth_gb,
    ROUND((MAX(total_size) - MIN(total_size))/1024/1024/30, 2) AS avg_daily_growth_mb
FROM (
    SELECT 
        o.owner,
        h.obj#,
        h.snap_id,
        SUM(h.space_allocated_total) OVER (PARTITION BY o.owner, h.snap_id) AS total_size
    FROM 
        dba_hist_seg_stat h
        JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
        JOIN dba_objects o ON h.obj# = o.object_id
    WHERE 
        s.begin_interval_time >= SYSDATE - 30
)
GROUP BY 
    owner
HAVING 
    MAX(total_size) > MIN(total_size)
ORDER BY 
    growth_gb DESC;

What this shows:

Growth by schema/owner
Average daily growth rate
Useful for multi-tenant or schema-per-application architectures

Query 4: Growth Rate Per Day (Detailed)

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A40
COLUMN mb_per_day FORMAT 999,990.99
COLUMN snapshots_analyzed FORMAT 999

SELECT 
    o.owner,
    o.object_name,
    ROUND((MAX(h.space_allocated_total) - MIN(h.space_allocated_total))/1024/1024/30, 2) AS mb_per_day,
    COUNT(DISTINCT h.snap_id) AS snapshots_analyzed
FROM 
    dba_hist_seg_stat h
    JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
    JOIN dba_objects o ON h.obj# = o.object_id
WHERE 
    s.begin_interval_time >= SYSDATE - 30
GROUP BY 
    o.owner, o.object_name
HAVING 
    MAX(h.space_allocated_total) > MIN(h.space_allocated_total)
ORDER BY 
    mb_per_day DESC
FETCH FIRST 10 ROWS ONLY;

What this shows:

Which objects are growing fastest per day
Number of snapshots included in calculation
Helps identify objects needing immediate attention

Capacity Planning with DBA_HIST_SEG_STAT

Predicting Storage Needs

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN tablespace_name FORMAT A30
COLUMN current_size_gb FORMAT 999,990.99
COLUMN growth_rate_gb_month FORMAT 999,990.99
COLUMN projected_90days_gb FORMAT 999,990.99
COLUMN projected_180days_gb FORMAT 999,990.99

SELECT 
    tablespace_name,
    ROUND(current_size/1024/1024/1024, 2) AS current_size_gb,
    ROUND(growth_rate/1024/1024/1024, 2) AS growth_rate_gb_month,
    ROUND((current_size + (growth_rate * 3))/1024/1024/1024, 2) AS projected_90days_gb,
    ROUND((current_size + (growth_rate * 6))/1024/1024/1024, 2) AS projected_180days_gb
FROM (
    SELECT 
        ts.name AS tablespace_name,
        MAX(h.space_allocated_total) AS current_size,
        (MAX(h.space_allocated_total) - MIN(h.space_allocated_total)) / 
            NULLIF(MONTHS_BETWEEN(MAX(s.begin_interval_time), MIN(s.begin_interval_time)), 0) AS growth_rate
    FROM 
        dba_hist_seg_stat h
        JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
        JOIN v$tablespace ts ON h.ts# = ts.ts#
    WHERE 
        s.begin_interval_time >= SYSDATE - 90
    GROUP BY 
        ts.name
)
WHERE 
    growth_rate > 0
ORDER BY 
    growth_rate DESC;

Use this for:

Storage capacity planning
Budget forecasting
Hardware procurement decisions

Identifying Growth Anomalies

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A40
COLUMN snap_date FORMAT A12
COLUMN daily_growth_mb FORMAT 999,990.99
COLUMN avg_growth_mb FORMAT 999,990.99
COLUMN deviation FORMAT 999,990.99

WITH daily_growth AS (
    SELECT 
        o.owner,
        o.object_name,
        TRUNC(s.begin_interval_time) AS snap_date,
        SUM(h.space_allocated_delta)/1024/1024 AS daily_growth_mb
    FROM 
        dba_hist_seg_stat h
        JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
        JOIN dba_objects o ON h.obj# = o.object_id
    WHERE 
        s.begin_interval_time >= SYSDATE - 30
    GROUP BY 
        o.owner, o.object_name, TRUNC(s.begin_interval_time)
),
growth_stats AS (
    SELECT 
        owner,
        object_name,
        snap_date,
        daily_growth_mb,
        AVG(daily_growth_mb) OVER (PARTITION BY owner, object_name) AS avg_growth_mb,
        STDDEV(daily_growth_mb) OVER (PARTITION BY owner, object_name) AS stddev_growth
    FROM 
        daily_growth
)
SELECT 
    owner,
    object_name,
    TO_CHAR(snap_date, 'YYYY-MM-DD') AS snap_date,
    ROUND(daily_growth_mb, 2) AS daily_growth_mb,
    ROUND(avg_growth_mb, 2) AS avg_growth_mb,
    ROUND(ABS(daily_growth_mb - avg_growth_mb), 2) AS deviation
FROM 
    growth_stats
WHERE 
    ABS(daily_growth_mb - avg_growth_mb) > (2 * stddev_growth)
    AND stddev_growth > 0
ORDER BY 
    deviation DESC;

Use this to identify:

Unusual growth spikes
Data load issues
Potential runaway processes

Using DBMS_SPACE Package

Oracle provides the DBMS_SPACE package for additional growth analysis:

Object Growth Trend Analysis

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN timepoint FORMAT A25
COLUMN space_usage FORMAT 999,999,999,999
COLUMN space_alloc FORMAT 999,999,999,999
COLUMN quality FORMAT A10

-- For regular tables
SELECT * 
FROM TABLE(
    DBMS_SPACE.OBJECT_GROWTH_TREND(
        object_owner => 'YOUR_SCHEMA',  -- Must exist and be correct
        object_name => 'YOUR_TABLE',
        object_type => 'TABLE'
    )
);

-- For partitioned tables
SELECT * 
FROM TABLE(
    DBMS_SPACE.OBJECT_GROWTH_TREND(
        object_owner => 'YOUR_SCHEMA',
        object_name => 'YOUR_TABLE',
        object_type => 'TABLE PARTITION',
        partition_name => 'YOUR_PARTITION'
    )
);

Note for DBMS_SPACE error: Replace YOUR_SCHEMA, YOUR_TABLE with actual values. The ORA-44001 error means schema name is invalid or doesn’t exist.

For Partitioned Tables

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
COLUMN growth_mb FORMAT 999,990.99

SELECT 
    o.owner,
    o.object_name AS table_name,
    o.subobject_name AS partition_name,
    ROUND((MAX(h.space_allocated_total) - MIN(h.space_allocated_total))/1024/1024, 2) AS growth_mb
FROM 
    dba_hist_seg_stat h
    JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
    JOIN dba_objects o ON h.obj# = o.object_id AND h.dataobj# = o.data_object_id
WHERE 
    s.begin_interval_time >= SYSDATE - 7
    AND o.subobject_name IS NOT NULL
    AND o.owner = 'YOUR_SCHEMA'
GROUP BY 
    o.owner, o.object_name, o.subobject_name
HAVING 
    MAX(h.space_allocated_total) > MIN(h.space_allocated_total)
ORDER BY 
    growth_mb DESC;

Note: DBMS_SPACE.OBJECT_GROWTH_TREND uses DBA_HIST_SEG_STAT internally but provides a simpler interface.

Troubleshooting Common DBA_HIST_SEG_STAT Issues

Issue 1: No Data in DBA_HIST_SEG_STAT

Symptom: Query returns 0 rows

Cause: AWR snapshots not configured or retention too short

Fix:

-- SQLPlus formatting
SET LINESIZE 200
COLUMN snap_interval FORMAT A20
COLUMN retention FORMAT A20

-- Check AWR configuration
SELECT 
    snap_interval,
    retention
FROM 
    dba_hist_wr_control;

-- Modify if needed (requires SYSDBA)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( -
    retention => 10080,  -- 7 days in minutes -
    interval  => 60      -- 1 hour snapshots -
);

Verify AWR is enabled:

SELECT 
    dbid,
    snap_id,
    begin_interval_time,
    end_interval_time
FROM 
    dba_hist_snapshot
WHERE 
    begin_interval_time >= SYSDATE - 1
ORDER BY 
    snap_id DESC
FETCH FIRST 10 ROWS ONLY;

Issue 2: Growth Data Doesn’t Match Reality

Symptom: DBA_HIST_SEG_STAT shows different sizes than DBA_SEGMENTS

Cause: AWR snapshots are historical, not real-time

Solution:

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A40
COLUMN hist_size_mb FORMAT 999,990.99
COLUMN current_size_mb FORMAT 999,990.99
COLUMN diff_mb FORMAT 999,990.99

SELECT 
    o.owner,
    o.object_name,
    ROUND(h.space_allocated_total/1024/1024, 2) AS hist_size_mb,
    ROUND(s.bytes/1024/1024, 2) AS current_size_mb,
    ROUND((s.bytes - h.space_allocated_total)/1024/1024, 2) AS diff_mb
FROM 
    dba_hist_seg_stat h
    JOIN dba_objects o ON h.obj# = o.object_id
    JOIN dba_segments s ON o.owner = s.owner AND o.object_name = s.segment_name
WHERE 
    h.snap_id = (SELECT MAX(snap_id) FROM dba_hist_snapshot)
    AND ABS(s.bytes - h.space_allocated_total) > 100*1024*1024  -- Diff > 100MB
ORDER BY 
    ABS(s.bytes - h.space_allocated_total) DESC;

Interpretation: Large differences indicate rapid growth between snapshots or manual AWR snapshot timing.

Issue 3: Partition Growth Tracking

Problem: Need to track individual partition growth

Solution:

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
COLUMN growth_mb FORMAT 999,990.99

SELECT 
    o.owner,
    o.object_name AS table_name,
    o.subobject_name AS partition_name,
    ROUND((MAX(h.space_allocated_total) - MIN(h.space_allocated_total))/1024/1024, 2) AS growth_mb
FROM 
    dba_hist_seg_stat h
    JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
    JOIN dba_objects o ON h.obj# = o.object_id AND h.dataobj# = o.data_object_id
WHERE 
    s.begin_interval_time >= SYSDATE - 7
    AND o.subobject_name IS NOT NULL  -- Only partitions
    AND o.owner = 'YOUR_SCHEMA'
GROUP BY 
    o.owner, o.object_name, o.subobject_name
HAVING 
    MAX(h.space_allocated_total) > MIN(h.space_allocated_total)
ORDER BY 
    growth_mb DESC;

Issue 4: AWR Retention vs. Analysis Period

Problem: Want to analyze more than retention period allows

Solution: Create a custom tracking table

-- Create tracking table
CREATE TABLE dba_growth_tracking (
    capture_date DATE,
    owner VARCHAR2(128),
    object_name VARCHAR2(128),
    tablespace_name VARCHAR2(30),
    size_bytes NUMBER,
    PRIMARY KEY (capture_date, owner, object_name)
);

-- Populate via scheduled job (run daily)
INSERT INTO dba_growth_tracking
SELECT 
    TRUNC(SYSDATE),
    owner,
    segment_name,
    tablespace_name,
    bytes
FROM 
    dba_segments
WHERE 
    owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP');

COMMIT;

Then query historical data:

-- SQLPlus formatting
SET LINESIZE 200
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A40
COLUMN growth_12months_gb FORMAT 999,990.99

SELECT 
    owner,
    object_name,
    ROUND((MAX(size_bytes) - MIN(size_bytes))/1024/1024/1024, 2) AS growth_12months_gb
FROM 
    dba_growth_tracking
WHERE 
    capture_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
GROUP BY 
    owner, object_name
HAVING 
    MAX(size_bytes) > MIN(size_bytes)
ORDER BY 
    growth_12months_gb DESC
FETCH FIRST 20 ROWS ONLY;

Issue 5: High-Frequency Growth Analysis

Problem: Hourly snapshots show too much noise

Solution: Smooth data using moving averages

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN snap_date FORMAT A12
COLUMN raw_growth_mb FORMAT 999,990.99
COLUMN smoothed_growth_mb FORMAT 999,990.99

SELECT 
    TO_CHAR(snap_date, 'YYYY-MM-DD') AS snap_date,
    ROUND(daily_growth/1024/1024, 2) AS raw_growth_mb,
    ROUND(AVG(daily_growth) OVER (ORDER BY snap_date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)/1024/1024, 2) AS smoothed_growth_mb
FROM (
    SELECT 
        TRUNC(s.begin_interval_time) AS snap_date,
        SUM(h.space_allocated_delta) AS daily_growth
    FROM 
        dba_hist_seg_stat h
        JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
        JOIN dba_objects o ON h.obj# = o.object_id
    WHERE 
        s.begin_interval_time >= SYSDATE - 30
        AND o.owner = 'YOUR_SCHEMA'
    GROUP BY 
        TRUNC(s.begin_interval_time)
)
ORDER BY 
    snap_date;

Best Practices for Using DBA_HIST_SEG_STAT

1. Configure Appropriate AWR Retention

Recommendation:

Minimum: 7 days (for weekly trend analysis)
Recommended: 30-45 days (for monthly capacity planning)
Extended: 90 days (for quarterly budget forecasting)

-- Check current settings
SELECT snap_interval, retention FROM dba_hist_wr_control;

-- Increase to 30 days
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 43200);

2. Regular Baseline Creation

Create AWR baselines for known growth periods:

-- Create baseline for month-end processing
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE( -
    start_snap_id => 1000, -
    end_snap_id   => 1100, -
    baseline_name => 'MONTH_END_DEC_2024' -
);

3. Automate Growth Reports

Create a scheduled job for weekly growth reports:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'WEEKLY_GROWTH_REPORT',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN generate_growth_report; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=8',
        enabled         => TRUE
    );
END;
/

4. Monitor AWR Space Usage

-- Check SYSAUX tablespace size
SELECT 
    tablespace_name,
    ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM 
    dba_segments
WHERE 
    tablespace_name = 'SYSAUX'
GROUP BY 
    tablespace_name;

-- Check AWR space consumption
SELECT 
    occupant_name,
    ROUND(space_usage_kbytes/1024/1024, 2) AS space_gb
FROM 
    v$sysaux_occupants
WHERE 
    occupant_name LIKE '%AWR%';

Combining with Other Oracle Views

DBA_HIST_SEG_STAT + DBA_HIST_TBSPC_SPACE_USAGE

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN tablespace_name FORMAT A30
COLUMN snap_date FORMAT A12
COLUMN used_gb FORMAT 999,990.99
COLUMN segment_growth_gb FORMAT 999,990.99

SELECT 
    ts.name AS tablespace_name,
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD') AS snap_date,
    ROUND(t.tablespace_usedsize * dt.block_size / 1024 / 1024 / 1024, 2) AS used_gb,
    ROUND(SUM(h.space_allocated_delta)/1024/1024/1024, 2) AS segment_growth_gb
FROM 
    dba_hist_tbspc_space_usage t
    JOIN dba_hist_snapshot s ON t.snap_id = s.snap_id
    JOIN v$tablespace ts ON t.tablespace_id = ts.ts#
    JOIN dba_tablespaces dt ON ts.name = dt.tablespace_name
    LEFT JOIN dba_hist_seg_stat h ON h.snap_id = t.snap_id AND h.ts# = t.tablespace_id
WHERE 
    s.begin_interval_time >= SYSDATE - 7
GROUP BY 
    ts.name,
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD'),
    t.tablespace_usedsize,
    dt.block_size
ORDER BY 
    ts.name, snap_date;

DBA_HIST_SEG_STAT + DBA_HIST_SQLSTAT

Find which SQL is causing object growth:

-- SQLPlus formatting
SET LINESIZE 200
SET PAGESIZE 50
COLUMN sql_id FORMAT A13
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A40
COLUMN executions FORMAT 999,999,999
COLUMN growth_mb FORMAT 999,990.99

SELECT 
    sq.sql_id,
    o.owner,
    o.object_name,
    SUM(sq.executions_delta) AS executions,
    ROUND(SUM(h.space_allocated_delta)/1024/1024, 2) AS growth_mb
FROM 
    dba_hist_sqlstat sq
    JOIN dba_hist_seg_stat h ON sq.snap_id = h.snap_id
    JOIN dba_objects o ON h.obj# = o.object_id
WHERE 
    sq.snap_id >= (SELECT MIN(snap_id) 
                  FROM dba_hist_snapshot 
                  WHERE begin_interval_time >= SYSDATE - 7)
    AND sq.executions_delta > 0
    AND h.space_allocated_delta > 0
GROUP BY 
    sq.sql_id, o.owner, o.object_name
ORDER BY 
    growth_mb DESC
FETCH FIRST 10 ROWS ONLY;

Related Oracle Database Guides

For comprehensive Oracle performance analysis:

For database administration:

Summary

DBA_HIST_SEG_STAT provides the foundation for comprehensive Oracle database growth analysis and capacity planning. By combining this view with DBA_HIST_SNAPSHOT and complementary Oracle views, you can create powerful monitoring and prediction systems for your database environment.

Key Takeaways:

✅ Use DBA_HIST_SEG_STAT for historical segment analysis – It’s your best tool for growth trend tracking

✅ Configure adequate AWR retention – At least 30 days for meaningful capacity planning

✅ Combine with current views – Use DBA_SEGMENTS for real-time validation

✅ Automate growth monitoring – Create scheduled reports to catch issues early

✅ Track at multiple levels – Monitor objects, schemas, tablespaces, and database level

✅ Plan proactively – Use growth trends to predict future storage needs

The key insight from using these historical views is that effective capacity planning requires both short-term monitoring (current snapshots) and long-term trend analysis (historical data). This dual approach enables proactive database management and prevents storage-related performance issues.

Let us know in the comments if you have questions about specific DBA_HIST_SEG_STAT scenarios or if you’ve developed additional monitoring techniques using these Oracle historical views.

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Introduction

Quick Reference: Essential DBA_HIST_SEG_STAT Queries

Top 10 Growing Segments (Last 7 Days)

Tablespace Growth Trend (Last 30 Days)

Daily Growth Rate

Why Use DBA_HIST_SEG_STAT for Growth Tracking?

Understanding DBA_HIST_SEG_STAT Columns

DBA_HIST_SEG_STAT vs Current Views

Historical Data Retention

Long-Term Tracking Strategy

Oracle Views for Growth Analysis

Understanding Space Allocation

DBA_HIST_SEG_STAT Practical Query Examples

Query 1: Object Growth Over Last 30 Days

Query 2: Tablespace Growth Trend Analysis

Query 3: Schema-Level Growth Summary

Query 4: Growth Rate Per Day (Detailed)

Capacity Planning with DBA_HIST_SEG_STAT

Predicting Storage Needs

Identifying Growth Anomalies

Using DBMS_SPACE Package

Object Growth Trend Analysis

For Partitioned Tables

Troubleshooting Common DBA_HIST_SEG_STAT Issues

Issue 1: No Data in DBA_HIST_SEG_STAT

Issue 2: Growth Data Doesn’t Match Reality

Issue 3: Partition Growth Tracking

Issue 4: AWR Retention vs. Analysis Period

Issue 5: High-Frequency Growth Analysis

Best Practices for Using DBA_HIST_SEG_STAT

1. Configure Appropriate AWR Retention

2. Regular Baseline Creation

3. Automate Growth Reports

4. Monitor AWR Space Usage

Combining with Other Oracle Views

DBA_HIST_SEG_STAT + DBA_HIST_TBSPC_SPACE_USAGE

DBA_HIST_SEG_STAT + DBA_HIST_SQLSTAT

Related Oracle Database Guides

Summary

Leave a Reply Cancel reply