Work With Results Through Technology to Grow Your Business Contact Us Today

Hide
Show
Categories: Technical Tips

It’s always better to know about a potential failure and fix it rather than have to scramble to find and allocate space when there is a failure.  This script will help identify (but not eliminate) impending space allocation failures. 

 The reason it’s not foolproof is that a segment taking an extent may cause another segment to not have enough space for it’s next extent.  Running this script daily  will catch most problems and if you “know your data” it will give you a heads up on what other segments will soon need attention.

 --******************************************************************
--
-- Script Name: fail_on_two.sql
--
-- Purpose: Determine if a Table, Index or Cluster segment will fail
--          when taking a next extent or if it will fail on taking 2 
--          extents

--
-- Description:  SELECT 1
-- Report on segment that will fail when taking 1 extent
--             UNION
--             SELECT 2
--             Report that there are no segments that will cause a failure
--             UNION
--             SELECT 3
--             Report on segment that will fail when taking 2 extents
--             UNION
--             SELECT 4
--             Report that there are no segments that will cause a failure
--             when taking 2 extents
--
--  Select statements 2 and 4 are optional but are included to produce 
--  output if everything is OK
--
-- Author: Scott Nelson
--         Results Through Technology, Inc.
--
--****************************************************************

 

set feedback off
set echo off
set feedback off
set echo off
set pagesize 60
set linesize 132
col bkup hea 'Backup Date' format a15
col numfiles hea 'Number of Files' format 999
col problem_segment hea 'SEGMENT IN TABLESPACE' format a40
col next_ext_2 hea 'NEXT_EXTENT Info' format 999,999,999
col next_ext hea 'NEXT_EXTENT Info' format 999,999,999
PROMPT Checking for fail on next extent AND fail on taking two extents
set verify off
SELECT Segment_Type||' '||Segment_Name||' in '||
       ts.ts#||':'||Tablespace_Name problem_segment,
       next_Extent*2 next_ext_2
FROM   dba_segments ds, sys.ts$ ts
WHERE  ds.segment_type in ('INDEX','TABLE','CLUSTER')
  AND  ds.next_extent*2 > (SELECT nvl(sum(dfs.bytes),0)
                           FROM dba_free_space dfs
                           WHERE dfs.bytes       >= ds.next_extent
                           AND ds.tablespace_name = dfs.tablespace_name)
  AND  1 < (SELECT count(segment_name)
            FROM   dba_segments ds2
            WHERE  ds2.tablespace_name = ds.tablespace_name)
              AND  ds.tablespace_name = ts.name
UNION
SELECT v.name||' has no fail on 2 extents' problem_segment,
       0 next_ext
FROM   v$database v
WHERE  not exists
       (SELECT 'x'
        FROM  dba_segments ds, sys.ts$ ts
        WHERE ds.segment_type in ('INDEX','TABLE','CLUSTER')
          AND ds.next_extent*2 > (SELECT nvl(sum(dfs.bytes),0)
                                  FROM dba_free_space dfs
                                  WHERE dfs.bytes       >= ds.next_extent
                                  AND ds.tablespace_name = dfs.tablespace_name)
          AND 1 < (SELECT count(segment_name)
                   FROM dba_segments ds2
                   WHERE ds2.tablespace_name = ds.tablespace_name)
                   AND ds.tablespace_name    = ts.name)
UNION
SELECT Segment_Type||' '||Segment_Name||' in '||
       ts.ts#||':'||Tablespace_Name  problem_segment,
       next_extent next_ext
FROM   dba_segments ds, sys.ts$ ts
WHERE  ds.segment_type in ('INDEX','TABLE','CLUSTER')
  AND  ds.next_extent >  (SELECT nvl(sum(dfs.bytes),0)
                          FROM   dba_free_space dfs
                          WHERE  dfs.bytes         >= ds.next_extent
                            AND  ds.tablespace_name = dfs.tablespace_name)
  AND  1 < (SELECT count(segment_name)
            FROM   dba_segments ds2
            WHERE  ds2.tablespace_name = ds.tablespace_name)
              AND  ds.tablespace_name  = ts.name
UNION
SELECT v.name||' has no fail on next extent' problem_segment,
       0 next_ext
FROM   v$database v
WHERE  not exists
       (SELECT 'x'
        FROM   dba_segments ds, sys.ts$ ts
        WHERE  ds.segment_type in ('INDEX','TABLE','CLUSTER')
          AND  ds.next_extent > (SELECT nvl(sum(dfs.bytes),0)
                                 FROM   dba_free_space dfs
                                 WHERE  dfs.bytes >= ds.next_extent
                                 AND  ds.tablespace_name = dfs.tablespace_name)
          AND  1 < (SELECT count(segment_name)
                    FROM   dba_segments ds2
                    WHERE  ds2.tablespace_name = ds.tablespace_name)
                      AND  ds.tablespace_name  = ts.name)
/

Spread the Word, like or share this page, your friends will also love it and thanks for it.

About the Author

Scott Nelson is an entrepreneur, Oracle wizard, consultant, businessman, filmmaker, inventor, tinkerer. He's always been willing to approach the unapproachable, attempt the impossible, reach for the brass ring in a wild dive into the darkness of the unknown because his motto is “All you have to do is do it” Currently living the good life in southern Indiana amongst the hills and pleasant valleys of Brown County.

Top