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

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Thursday, February 03, 2011 4:33 AM To: 'sheldonquinny_at_gmail.com'; 'Oracle-L Freelists' Subject: RE: Based of the Tablespace Growth Script I need to define a query.  

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 Stephane Faroult wrote that should work quite nicely. 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.  

<snip>

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