Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: failed to extent to the next
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,
FREE_SEGMENTS, FREE_BLOCKS, FREE_BYTES, FREE_MBYTES,
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_mbytesfrom 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_usedfrom sys.tsfile_space_view
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:Joan.Hsieh_at_mirror-image.com] > > 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: mark_at_cool-tools.co.uk 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: ListGuru_at_fatcity.com (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).Received on Fri Apr 06 2001 - 10:15:19 CDT
![]() |
![]() |