RE: TABLESPACE ALERT

From: CHAN Chor Ling Catherine (CSC) <clchan_at_nie.edu.sg>
Date: Tue, 08 Jan 2002 17:37:54 -0800
Message-ID: <F001.003EA85E.20020108172023@fatcity.com>

Hi Seema,

I've a weekly job that informs me "List Of Tablespaces With Next Extent > Largest Free Extent". This list helps me to identify the list of tablespaces that need to be extended due to lack of space (This script will take into considerations the autoextend indicator). If this is what you were looking for, below is the sql statement. I am using Oracle 8.1.6.

Hope it helps.

Regds,
New Bee

REM

REM     NAME         :  fail_ext_segment.sql
REM     FUNCTION     :  To identify segments that will fail to acquire
REM                     their next extent.
REM     HISTORY      :
REM             DATE            WHO             WHAT
REM             ----            ---             ----
REM             07-Nov-2001     CHORLING        CREATION

REM connect / as sysdba

SET PAGESIZE 60
SET LINESIZE 132
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
SET HEADING OFF

SELECT  'Database :  '||NAME||'     Time :  '||
        TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS')

FROM V$DATABASE; SELECT 'Report : List Of Segments With Next Extent > Largest Free Extent'
FROM DUAL; SET TERMOUT OFF
REM SET NEWPAGE 0
SET HEADING ON
SET SPACE 1 COLUMN OWNER FORMAT A10
COLUMN SEGMENT_NAME FORMAT A30
COLUMN SEGMENT_TYPE FORMAT A10
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN SEGMENT_TYPE HEADING "Segment|Type" COLUMN EXTENTS_ALLOC HEADING "EXTENTS|ALLOC" format 999,999 COLUMN MAX_FREE_BYTES FORMAT 999,999,999

COLUMN NEXT_EXTENT_BYTES FORMAT 999,999,999
COLUMN AUTO_EXTEND_TABLESPACE FORMAT A7
COLUMN NEXT_EXTENT_BYTES HEADING "Next|Extent|Bytes"

COLUMN MAX_FREE_BYTES HEADING "Largest|Free|Extent" COLUMN TABLESPACE_NAME HEADING "Tablespace|Name" COLUMN AUTO_EXTEND_TABLESPACE HEADING "Auto|Extend"

BREAK ON OWNER SKIP 1 SELECT
T.OWNER,T.SEGMENT_NAME,T.SEGMENT_TYPE,F.TABLESPACE_NAME,T.NEXT_EXTENT_BYTES, T.Extents_alloc,F.MAX_FREE_BYTES,DF.AUTO Auto_Extend_Tablespace FROM (SELECT
OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,MAX(NEXT_EXTENT) NEXT_EXTENT_BYTES,

      SUM(EXTENTS) Extents_alloc
      FROM DBA_SEGMENTS GROUP BY
OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME) T,


     (SELECT TABLESPACE_NAME,MAX(BYTES) MAX_FREE_BYTES FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,


     (SELECT TABLESPACE_NAME,MAX(AUTOEXTENSIBLE) AUTO FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) DF
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME

AND  F.MAX_FREE_BYTES <= T.NEXT_EXTENT_BYTES
AND DF.TABLESPACE_NAME=T.TABLESPACE_NAME
AND DF.AUTO='NO'

union
SELECT
T.OWNER,T.SEGMENT_NAME,T.SEGMENT_TYPE,F.TABLESPACE_NAME,T.NEXT_EXTENT_BYTES, T.Extents_alloc,F.MAX_FREE_BYTES,'Yes' Auto_Extend_Tablespace FROM (SELECT TABLESPACE_NAME,(MAXBYTES-USER_BYTES) MAX_FREE_BYTES,AUTOEXTENSIBLE AUTO

      FROM DBA_DATA_FILES WHERE AUTOEXTENSIBLE='YES') F,


     (SELECT

OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,MAX(NEXT_EXTENT) NEXT_EXTENT_BYTES,

      SUM(EXTENTS) Extents_alloc
      FROM DBA_SEGMENTS GROUP BY

OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME AND F.MAX_FREE_BYTES <= T.NEXT_EXTENT_BYTES ORDER BY 1,4,3,2;

                -----Original Message-----
                From:   Seema Singh [mailto:oracledbam_at_hotmail.com]
                Sent:   Wednesday, January 09, 2002 8:00 AM
                To:     Multiple recipients of list ORACLE-L
                Subject:        TABLESPACE ALERT

                Hi
                I want to set alert information whenever a tablespaces reach
close to full 
                or some particular space is free.
                If some one have any scripts please send me.
                Thanks
                -Seema



        
_________________________________________________________________
                Send and receive Hotmail on your mobile device:

http://mobile.msn.com

                -- 
                Please see the official ORACLE-L FAQ: http://www.orafaq.com
                -- 
                Author: Seema Singh
                  INET: oracledbam_at_hotmail.com

                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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: CHAN Chor Ling Catherine (CSC)
  INET: clchan_at_nie.edu.sg

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 Tue Jan 08 2002 - 19:37:54 CST