Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle growth reports

Oracle Database Tips by Donald Burleson

By Steve Karam, OCM, Oracle ACE

Also see dba_hist_tablespace_stat tips and how to use v$datafile to track database growth.

Also see AWR solutions to table growth reports. and tracking row counts and block changes within tables. Also we have the ability to track the growth of the whole schema and database

Oracle provides time-series segment statistics for tables and index in the 10g Automated workload Repository tables (AWR). These can quickly extract data for Oracle growth reports.

Note: for tracking table and index growth over time, see the dba_hist_seg_stat table.

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, total database growth reports are 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).

Below is a great script to display the total Oracle growth between two periods. This script can be modified to display overall database growth trends for capacity planning purposes. You can also see the growth of key tables and indexes with this Oracle growth tracking script. Oracle growth report at the table level

set feedback off

set pages 80

set linesize 150

spool /tmp/weekly_growth.txt

ttitle "Total Disk Used"

select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",

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
see code depot for full script

where end_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.owner = '&schema_name'

and space_used_delta > 0;

ttitle "Total Disk Used by Object Type"

select c.segment_type, sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Space (M)",

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
see code depot for full script

where end_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 space_used_delta > 0

and c.owner = '&schema_name'

group by rollup(segment_type);

spool off

A sample of this report shows the total database growth between the two snapshot periods.

Get the Complete
Oracle SQL Tuning Information

The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

��