| |
 |
|
Oracle table growth reports
Oracle Tips by Burleson Consulting |
Oracle provides time-series segment statistics
for tables and index in the 10g Automated workload Repository tables
(AWR).
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, 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.
Oracle table growth reports.
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
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.
 |
If you like Oracle tuning, check-out my
latest book "Oracle Tuning: The Definitive Reference",
the best deal at 30% off, buying directly from the
publisher.
Packed with almost 1,000 pages of Oracle performance tuning
techniques, it's the foolproof way to find and correct
Oracle bottlenecks. |
|
