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

  • From: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
  • To: sheldonquinny@xxxxxxxxx
  • Date: Thu, 3 Feb 2011 13:04:21 +0000

Assuming you are on 11g this query taken from data in the awr repository
should give you some idea of what can be achieved

Cheers,

Ian

select * from
(
select name,tablespace_usedsize,tday from
(
select name,
       tablespace_size,
       tablespace_usedsize,
       rtime,
       to_date(substr(rtime,1,10),'mm/dd/yyyy') tday,
       max(rtime) over (partition by
name,to_date(substr(rtime,1,10),'mm/dd/yyyy')) rmax
from   sys.wrh$_tablespace_space_usage,
       v$tablespace
where  tablespace_id = ts#
)
where rtime = rmax
)
pivot
(max(tablespace_usedsize) for (tday) in
('27-JAN-11','28-JAN-11','29-JAN-11','30-JAN-11','01-FEB-11','02-FEB-11','03-FEB-11'))



|---------+----------------------------->
|         |           sheldonquinny@gmai|
|         |           l.com             |
|         |           Sent by:          |
|         |           oracle-l-bounce@fr|
|         |           eelists.org       |
|         |                             |
|         |                             |
|         |           03/02/2011 10:48  |
|         |           Please respond to |
|         |           sheldonquinny     |
|         |                             |
|---------+----------------------------->
  
>--------------------------------------------------------------------------------------------------------------------------------------------------|
  |                                                                             
                                                                     |
  |       To:       mwf@xxxxxxxx                                                
                                                                     |
  |       cc:       oracle-l@xxxxxxxxxxxxx                                      
                                                                     |
  |       Subject:  Re: Based of the Tablespace Growth Script I need to define 
a query.                                                              |
  |                                                                             
                                                                     |
  |                                                                             
                                                                     |
  
>--------------------------------------------------------------------------------------------------------------------------------------------------|




Hi,

Thanks for the input...I will look into in ..and see what comes out of it.

On Thu, Feb 3, 2011 at 1:28 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
Then you need to be sure your underlying data is a regular time series and
use lag  (as per Stephane’s suggestion) and pivot. If prior to 11g, look up
how to pivot without the new pivot syntax, very well described by Tom Kyte;
putting kyte pivot into your favorite search engine should get you there
quickly if you skip any known bad sites. Or buy his book.





mwf





From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Sheldon Quinny
Sent: Thursday, February 03, 2011 4:47 AM
To: Mark W. Farnham
Cc: Oracle-L Freelists
Subject: Re: Based of the Tablespace Growth Script I need to define a
query.





Hi Mark,

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.

(Embedded image moved to file: pic13966.gif)02.png



<snip>







This email was received from the INTERNET and scanned by the Government
Secure Intranet anti-virus service supplied by Cable&Wireless Worldwide in
partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) In
case of problems, please call your organisation?x02019;s IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.

For the latest data on the economy and society consult National Statistics at 
http://www.ons.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for 
compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not 
necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure 
Intranet virus scanning service supplied by Cable&Wireless Worldwide in 
partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On 
leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or 
recorded for legal purposes.

Attachment: pic13966.gif
Description: GIF image

Other related posts: