This guide provides an SQL script to monitor table size changes in Oracle over the last N days, helping you analyze storage utilization trends efficiently.
Table of Contents
Querying Oracle Table Size Changes Over the Last N Days:
set linesize 200
set pages 100
set feedback off
column "Space Used(MB)" justify right format 9,999,999.99
column "Total Object Size(MB)" justify right format 9,999,999.99
column "Total Disk Usage(%)" justify right format 999.99
select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, sum(space_used_delta)/1024/1024 "Space used(MB)", avg(seg.bytes)/1024/1024 "Total Object Size(MB)",
round(sum(space_used_delta)/sum(seg.bytes)*100,2) "Total Disk Usage(%)"
from
dba_hist_snapshot hsn,
dba_hist_seg_stat hss,
dba_objects objc,
dba_segments seg
where begin_interval_time > trunc(sysdate) - &days_back
and hsn.snap_id = hss.snap_id
and objc.object_id = hss.obj#
and objc.owner = seg.owner
and objc.object_name = seg.segment_name
and seg.segment_name = '&segment_name'
group by to_char(end_interval_time, 'MM/DD/YY'))
order by to_date(mydate, 'MM/DD/YY');
Sample Output:
Tracking table growth in Oracle is an essential part of database maintenance. By using the SQL query provided in this guide, DBAs can monitor Oracle database table growth trends, optimize storage allocation, and enhance database performance.
If you want to find the top 10 largest tables in Oracle, follow my article, Top 10 largest tables in Oracle.
For further reading on Oracle database storage management, refer to the resource: Oracle Database Documentation
