Estimating Size of Oracle Database Objects --seeing history of object/table size

Resource and performance capacity of the servers is one side of the puzzle. Equally important is to size/estimate the database for storage and the data growth. This would mean the database, the database objects, and the underlying storage subsystem would also have to be sized for today and tomorrow.

Oracle provides few packages and procedures that help determine the size of objects and indexes based on the   estimated growth size. Even further, using the DBMS_SPACE.OBJECT_GROWTH_TREND function, a growth pattern for existing tables can be obtained.

The following query will list the object growth trend for an object; the data for the trend listed is gathered from Automatic Workload Repository (AWR). The growth trends for two of the tables are listed following.

The OBJECT_GROWTH_TREND function returns four values:

• TIMEPOINT—Is a time stamp value indicating the time of the recording/reporting.

• SPACE_USAGE—Lists the amount of space used by the object at the given point in time.

• SPACE_ALLOCATED—Lists the amount of space allocated to the object in the table space at the

given point in time.

• QUALITY—Indicates the quality of data reported; there are three possible values:

• INTERPOLATED—The value did not meet the criteria of GOOD. As noted in the outputs

following, the used and allocated are same. Basically, the values do not reflect any usage.

• GOOD—The value whenever the value of TIME is based on recorded statistics. Value is

marked good if at least 80% of the value is derived from GOOD instance values.

• PROJECTED—The value of time is in the future as of the time the table was produced.

In a RAC environment, the output reflects the aggregation of values recorded across all instances in the cluster.

SELECT *

FROM TABLE(dbms_space.object_growth_trend(object_owner => 'ABDUL',

 object_name => 'HISTORY', object_type => 'TABLE'));

TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY

------------------------------- ----------- ----------- --------------------

28-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

29-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

30-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

31-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

01-JUN-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

02-JUN-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

03-JUN-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

04-JUN-14 11.12.43.052162 AM 131877793 134217728 GOOD

05-JUN-14 11.12.43.052162 AM 132003569 134369941 PROJECTED

06-JUN-14 11.12.43.052162 AM 132129344 134522153 PROJECTED

07-JUN-14 11.12.43.052162 AM 132255119 134674366 PROJECTED

08-JUN-14 11.12.43.052162 AM 132380894 134826579 PROJECTED

09-JUN-14 11.12.43.052162 AM 132506669 134978791 PROJECTED