Monitoring tablespaces
- From: "Kline.Michael" <Michael.Kline@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Sep 2005 08:32:07 -0400
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?
a. 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@xxxxxxxxxxxx> michael.kline@xxxxxxxxxxxx
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]
Other related posts:
