RE: failed to extent to the next

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Fri, 06 Apr 2001 08:15:19 -0700
Message-ID: <F001.002E4088.20010406075357@fatcity.com>

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: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