Re: Based of the Tablespace Growth Script I need to define a query.

Thanks for the 5 para answer that really made me want to grab a coffee...

First of all , this all is still on the test systems, so I would take your operational cost advice.
Second off..the main idea for me is to generate a report for the increase in tablespace each month and email it to the management.

The dig mention below is of what I would want ....end of the day.

[image: 02.png]

On Thu, Feb 3, 2011 at 12:33 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> It appears you are storing the results of calculations in your table.
> Unless calculations have a very high operational order that is usually a
> waste. For example, you could easily calculate usedspace from
> tablespacesize-freespace and percentage from
> round(freespace/tablespacesize), and store only two column instead of 4. If
> this is a view doing exactly this on a table, forgive me for being fooled by
> what you wrote.
>
>
>
> Is your definition of growth simply the delta in usedspace, or do you want
> to track more than one growth characteristic? For surprise avoidance,
> percentage free is less desirable than number of extents remaining available
> compared to rate of extent allocation from history (average and maximum past
> rates of extention are more useful, seasonally adjusted rates essential in
> some cases, and of course with tablespace autoextention available space to
> consume on the existing deployed media is also a useful thing to know.)
>
>
>
> Ah, you do have an actual question. Probably the most straightforward way
> to do this from your columns listed is to join two timestamp filtered
> subsets of the table on “schema” and calculate, perhaps
> old.usedspace-new.usedspace. If schema,tstamp is not unique at some useful
> trunc level, then you’ll need to decide how to pick which one you want
> within a given day.
>
>
>
> Conceptually it is useful to think of this as if you had stored the views
> end_dec2010_tbs_size and end_jan2011_tbs_size returning the one row want for
> each “schema” for each time. The various ways of achieving inline views
> obviate the need to store all those views and facilitate having a single
> query with the two times to compare as parameters.
>
>
>
> If your underlying data is amenable to the analytic function lag(), then as
> a previous poster wrote that should work. For example, if you store one set
> of rows per “schema” per month that could work quite nicely at the default
> offset of 1. If you store daily sets of rows you’ll need to monkey around
> with the offset to mimic Jan 31, Feb 28(29), Mar 31, Apr 30, etc., depending
> on what month. If your data is not a regular time series with respect to
> “tstamp” then analytic functions are probably not the answer. Paired
> filtered views can be done even if you have multiple rows per day by
> selecting, perhaps, the max time value within the desired day.
>
>
>
> mwf
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Sheldon Quinny
> *Sent:* Thursday, February 03, 2011 1:05 AM
> *To:* Oracle-L Freelists
> *Subject:* Based of the Tablespace Growth Script I need to define a query.
>
>
>
>
> Hi All,
>
> Based on the given output below, I am looking for a query that would show
> me each tablespaces growth per month. Based on the total tablespace growth.
>
> The Below output collectes statitics for each month for the tablespaces.
>
>
> [image: DB_Hist.jpg]
>
>
> Thanks in Advance..
>
> Regards,
> Sheldon.
>

--
http://www.freelists.org/webpage/oracle-l