Oracle provides time-series segment statistics
for tables and index in the 10g Automated workload Repository tables
(AWR).
Also see tracking
row counts and block changes within tables. wri$_optstat_tab_history is
only one of several ways to track table growth. Also
see
tracking
row counts and block changes within tables.
Also we have the
ability to track the growth of the whole schema and database
and
database growth reports.
If you are pre-Oracle10g you can write your own
tables to periodically examine and store object sizes for database
growth reports by creating your own STATSPACK extension tables (See
book "Oracle Tuning: The Definitive Reference",
for ready to use scripts to create a stats$tab_stats
and stats$idx_stats
tracking tables).
In Oracle 10g and beyond, displaying table growth is easy
because the dba_hist_seg_stat tables provides the delta value for
every table and index (Note: the "total" columns are not
implemented as of Oracle 10.0.2).
You can also see the growth of the whole
database with this Oracle growth tracking script.
Below is a great script to display table size
changes between two periods.
column "Percent of Total Disk Usage" justify right format 999.99
column "Space Used (MB)" justify right format 9,999,999.99
column "Total Object Size (MB)" justify right format
9,999,999.99
set
linesize 150
set
pages 80
set
feedback off
select * from (select to_char(end_interval_time, 'MM/DD/YY')
mydate, sum(space_used_delta) / 1024 / 1024 "Space used (MB)",
avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of
Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where
see code depot
download for full scripts
begin_interval_time > trunc(sysdate) - &days_back
and
sn.snap_id = a.snap_id
and
b.object_id = a.obj#
and
b.owner = c.owner
and
b.object_name = c.segment_name
and
c.segment_name = '&segment_name'
group
by to_char(end_interval_time, 'MM/DD/YY'))
order
by to_date(mydate, 'MM/DD/YY');
A sample of this report show the total database
growth between the two snapshot periods.
Also see using v$datafile
to track database growth
Historical table & index growth reports
select * from (SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE , t.NAME "Tablespace", s.growth/(1024*1024) "Growth in MB", (SELECT sum(bytes)/(1024*1024*1024) FROM dba_segments WHERE segment_name=o.object_name) "Total Size(GB)" FROM DBA_OBJECTS o, ( SELECT TS#,OBJ#, SUM(SPACE_USED_DELTA) growth FROM DBA_HIST_SEG_STAT GROUP BY TS#,OBJ# HAVING SUM(SPACE_USED_DELTA) > 0 ORDER BY 2 DESC ) s, v$tablespace t WHERE s.OBJ# = o.OBJECT_ID AND s.TS#=t.TS# and o.owner='XXXXXX' and o.object_type='TABLE' ORDER BY 6 DESC) where rownum<20;
|