Database Reference
In-Depth Information
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 => 'RAPUSR',
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
Search WWH ::
Custom Search