Re: DBMS_SPACE built-in

"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.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005