Here is a pretty cool way to monitor your tablespaces, and file size
growths, just create the following view (as SYS), and send the results to
the following script to your email daily:
CREATE OR REPLACE VIEW TSFILE_SPACE_VIEW
( TS#,
NAME,
STATUS,
CONTENTS,
LOGGING,
EXTENT_MANAGEMENT,
FILE#,
FILE_NAME,
BLOCKSIZE,
TOTAL_BYTES,
TOTAL_BLOCKS,
TOTAL_MBYTES,
FILE_STATUS,
AUTOEXTENSIBLE,
MAXBYTES,
MAXBLOCKS,
INCREMENT_BY,
FREE_SEGMENTS,
FREE_BLOCKS,
FREE_BYTES,
FREE_MBYTES,
LARGEST_SEGMENT_BLOCKS,
LARGEST_SEGMENT_MBYTES )
AS
select
d.ts# ts#,
a.tablespace_name name,
a.status,
decode (a.contents,'PERMANENT','PERM','TEMPORARY','TEMP') contents,
decode (a.logging, 'LOGGING','Yes','No') logging,
a.extent_management,
b.file_id file#,
substr(b.file_name,1,40) file_name,
b.bytes/b.blocks blocksize,
b.bytes total_bytes,
b.blocks total_blocks,
b.bytes/(1024*1024) total_mbytes,
b.status file_status,
b.autoextensible autoextensible,
b.maxbytes maxbytes,
b.maxblocks maxblocks,
b.increment_by increment_by,
nvl(count(c.block_id),0) free_segments,
sum(nvl(c.blocks,0)) free_blocks,
sum(nvl(c.bytes,0)) free_bytes,
sum(nvl(c.bytes,0))/(1024*1024) free_mbytes,
max(nvl(c.blocks,0)) largest_segment_blocks,
(max(nvl(c.bytes,0)))/(1024*1024) largest_segment_mbytes
from dba_tablespaces a, dba_data_files b, dba_free_space c, sys.ts$ d
where a.tablespace_name = b.tablespace_name and
b.relative_fno = c.relative_fno and
a.tablespace_name = d.name
group by d.ts#,
a.tablespace_name,
a.status,
a.contents,
a.logging,
a.extent_management,
b.file_id,
b.file_name,
b.bytes,
b.blocks,
b.status,
b.autoextensible,
b.maxbytes,
b.maxblocks,
b.increment_by
/
select ts#,
name,
blocksize,
status,
contents,
logging,
extent_management,
sum(total_blocks) total_blocks,
sum(total_bytes) total_bytes,
sum(total_mbytes) total_mbytes,
sum(free_segments) free_segments,
sum(free_blocks) free_blocks,
sum(free_mbytes) free_mbytes,
max(largest_segment_blocks) largest_segment_blocks,
max(largest_segment_mbytes) largest_segment_mbytes,
100 - ((sum(free_blocks) * 100) / sum(total_blocks)) pct_used
from sys.tsfile_space_view
group by ts#,
name,
blocksize,
status,
contents,
logging,
extent_management
TS# NAME BLOCKSIZE STATUS CONT LOG
---------- ------------------------------ ---------- --------- ---- ---
EXTENT_MAN TOTAL_BLOCKS TOTAL_BYTES TOTAL_MBYTES FREE_SEGMENTS FREE_BLOCKS
---------- ------------ ----------- ------------ ------------- -----------
FREE_MBYTES LARGEST_SEGMENT_BLOCKS LARGEST_SEGMENT_MBYTES PCT_USED
----------- ---------------------- ---------------------- ----------
0 SYSTEM 8192 ONLINE PERM Yes
DICTIONARY 35072 287309824 274 4 894
6.984375 774 6.046875 97.450958
Format as needed. The good thing about this is the percent usage. If you
have say 80-90% usage of a tablespace, that isn't static, then you start
looking at adding more space.. Like this SYSTEM tablespace :)
HTH
Mark
-----Original Message-----
Sent: Friday, April 06, 2001 02:55
To: Multiple recipients of list ORACLE-L
Well, if next_extent=250m, pctincrease=0. bytes *12 >= next_extent means
dba_free_space must have a (bytes*12) large space to satisfy the condition.
But I want 12 slots >250m, if I get less than 12 slots < 250m, I will get
email. Gee, I am confused myself, am I right?
Joan
-----Original Message-----
Kilchoer
Sent: Thursday, April 05, 2001 5:51 PM
To: Multiple recipients of list ORACLE-L
> -----Original Message-----
> From: Joan Hsieh [mailto:[EMAIL PROTECTED]]
>
> I used to use this script detect the tablespace fail to
> extent to the next
> and sent email to me everyday. It works fine. However, the
> other dba think
> next extent is not good enough to get quick responds since we
> have so much
> volume transactions going on. He want to detect the
> tablespace can't extent
> to the next 12 extents. I tried couple ways, (bytes * 12)
> but that result
> not 100% correct. Thanks in advance if someone can share your ideas.
What do you mean by "not 100% correct"? How do you know? Have you considered
the pct_increase factor for the next_extent?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
