Monitoring tablespaces

Due to the massive sizes of a set of databases, I'm thinking I may need to monitor my tablespaces. (HP-UX and 9.2.0.6)  

These are a lot of "batch runs" so to speak that generate massive amounts of data and then, once posted and aggregated, they often get purged back out. To cover this massive expansion I often use autoextend to make sure I'm covered, but then am faced with having to remember to shrink the datafiles back down because often there are multiple sets, like M1, M2, M3, etc. So I often don't have enough space to allow ALL of them to expand and stay there.  

The other thing that often catches me is expansions into autoextend may take place for special occasions and then I find I'm "reserving" way too much space. One tablespace I found went from an average working set of 70-80 GB to now only needing 50-55GB. So I was holding 15 to 25GB too much, and this times two.  

What I'd like to pick up is the ability to find the following things:  

1. What is the largest size this tablespace has hit during history?
2. What is the average size?
3. Which tablespaces have "cleared" yet still hold massive amounts of space?
4. Some of these can clear out 50GB and may not be used again for 60 days.

If anyone has already done this via a few scripts and would like to share, that would be great. Probably would save me several hours.  

With the "bug" in 9.2.0.6 in dba_segments on partitions, I may have to use dba_free_space or dba_extents to calculate space.  

Roughly, figuring I'd "trunc" the date and perhaps have tablespace and free_space or used_space if I can be sure it's accurate. I may also have to include auto_extend and total size as I'm adjusting these all the time. If I log a "daily" entry, I should be able to capture all of this that I want above.  

Michael Kline
Database Administration
SunTrust Banks, Inc.
Mail Code TOC-7505

1030 Wilmer Avenue
Richmond, Va. 23227
Tel: 804.261.9446 Net: 643.9446

Cell: 804.744.1545
 <mailto:michael.kline_at_suntrust.com> michael.kline_at_suntrust.com  

Seeing beyond Money(sm)         

LEGAL DISCLAIMER
The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.   

Seeing Beyond Money is a service mark of SunTrust Banks, Inc. [ST:XCL]        

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