Jonathan Lewis wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1132968307.124629_at_yasure...
>
>>I've been taking advantage of the holiday to update many of the entries
>>in Morgan's Library and decided to build demos of the of the
>>functionality in the DBMS_SPACE package: Some documented, some
>>undocumented.
>>
>>Found a real gem ... object_trend_growth which is a pipelined table
>>function where each returned row describes the space usage of the
>>object at a specific point in time.
>>
>>dbms_space.object_growth_trend (
>>object_owner IN VARCHAR2,
>>object_name IN VARCHAR2,
>>object_type IN VARCHAR2,
>>partition_name IN VARCHAR2 DEFAULT NULL,
>>start_time IN TIMESTAMP DEFAULT NULL,
>>end_time IN TIMESTAMP DEFAULT NULL,
>>interval IN DSINTERVAL_UNCONSTRAINED DEFAULT NULL,
>>skip_interpolated IN VARCHAR2 DEFAULT 'FALSE',
>>timeout_seconds IN NUMBER DEFAULT NULL,
>>single_datapoint_flag IN VARCHAR2 DEFAULT 'TRUE') RETURN
>>object_growth_trend_table PIPELINED;
>>
>>Return Type:
>>TYPE object_growth_trend_row IS RECORD (
>>timepoint TIMESTAMP,
>>space_usage NUMBER,
>>space_alloc NUMBER,
>>quality VARCHAR(20));
>>
>>-- sample
>>SELECT * FROM TABLE(
>>dbms_space.object_growth_trend('UWCLASS','SERVERS','TABLE'));
>>
>>What I like about it most is the following from the output:
>>
>>TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
>>----------------------------------- ----------- ----------- ------------
>>26-NOV-05 05.18.16.383000 PM 17032 262144 PROJECTED
>>27-NOV-05 05.18.16.383000 PM 17032 262144 PROJECTED
>>28-NOV-05 05.18.16.383000 PM 17032 262144 PROJECTED
>>29-NOV-05 05.18.16.383000 PM 17032 262144 PROJECTED
>>30-NOV-05 05.18.16.383000 PM 17032 262144 PROJECTED
>>
>>Yes ... it is projecting the size of a table into the future based on
>>past usage. The output is one row per day going back 30 days.
>>
>>A Thanksgiving present to any and all.
>>--
>>Daniel A. Morgan
>>http://www.psoug.org
>>damorgan_at_x.washington.edu
>>(replace x with u to respond)
>
>
>
> One minor detail with that - it references
> some of the wrh$ tables (workload
> repository history) so if you don't have
> the extra licences for the diagnostic
> and performance tuning packs, you aren't
> allowed to run that procedure.
A good point. Thanks for making it.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Sat Nov 26 2005 - 12:59:26 CST
