Platinum Solutions Corporate Website


Finding Growth Problems

The answer you entered to the math problem is incorrect.

I came across the following code that lists all tablespace objects with growth problems  (specifically, ones that will fail when they try to grab another extent).  It works for both Locally-Managed and Dictionary-Managed tablespaces (in the case of locally-managed that use AutoAllocate extent management, the script assumes that all the extent sizes are the same):

Set Linesize 130
SET Linesize 130
SET Trims on
COLUMN Tablespace             Format A30
COLUMN SegmentType            Format A12
COLUMN Owner                  Format A12
COLUMN Segment                Format A30
COLUMN "Required Extent(KB)"  Format 999,999,999.99
COLUMN "MaxAvail(KB)"         Format 999,999,999.99

SELECT   /*+ RULE */
         seg.tablespace_name "Tablespace", seg.segment_type "SegmentType",
         ext.owner, ext.segment_name "Segment",
           DECODE
                 (freespace.extent_management,
                  'DICTIONARY', seg.next_extent,
                  'LOCAL', DECODE (freespace.allocation_type,
                                   'UNIFORM', freespace.initial_extent,
                                   'SYSTEM', ext.BYTES
                                  )
                 )
         / 1024 AS "Required Extent(KB)",
         freespace.largest / 1024 "MaxAvail(KB)"
    FROM dba_extents ext,
         dba_segments seg,
         (SELECT   /*+ RULE */
                   maxsize_perfile.tablespace_name, tbs.extent_management,
                   tbs.allocation_type, tbs.initial_extent, tbs.next_extent,
                   MAX (maxsize_perfile.maxsizebytes) AS largest
              FROM (SELECT   /*+ RULE */
                             ddf.tablespace_name, ddf.file_id,
                               DECODE (autoextensible,
                                       'YES', (ddf.maxbytes - ddf.BYTES),
                                       0
                                      )
                             + NVL (MAX (dfs.BYTES), 0) AS maxsizebytes,
                             NVL (MAX (dfs.BYTES),
                                  0
                                 ) AS maxfreeextentsizebytes
                        FROM dba_free_space dfs,
                             dba_data_files ddf,
                             dba_tablespaces tbsp
                       WHERE dfs.file_id(+) = ddf.file_id
                         AND tbsp.tablespace_name = ddf.tablespace_name
                         AND tbsp.CONTENTS = 'PERMANENT'
                         AND tbsp.status = 'ONLINE'
                    GROUP BY ddf.tablespace_name,
                             ddf.file_id,
                             DECODE (autoextensible,
                                     'YES', (ddf.maxbytes - ddf.BYTES),
                                     0
                                    )) maxsize_perfile,
                   dba_tablespaces tbs
             WHERE maxsize_perfile.tablespace_name = tbs.tablespace_name
               AND tbs.status = 'ONLINE'
          GROUP BY maxsize_perfile.tablespace_name,
                   tbs.extent_management,
                   tbs.allocation_type,
                   tbs.initial_extent,
                   tbs.next_extent) freespace
   WHERE seg.owner = ext.owner
     AND seg.segment_type = ext.segment_type
     AND seg.segment_name = ext.segment_name
     AND seg.tablespace_name = ext.tablespace_name
     AND (seg.extents - 1) = ext.extent_id
     AND seg.tablespace_name = freespace.tablespace_name
     AND DECODE (freespace.extent_management,
                 'DICTIONARY', seg.next_extent,
                 'LOCAL', DECODE (freespace.allocation_type,
                                  'UNIFORM', freespace.initial_extent,
                                  'SYSTEM', ext.BYTES
                                 )
                ) > freespace.largest
ORDER BY seg.tablespace_name, seg.segment_type, seg.segment_name

 

Comments

Post new comment

Please solve the math problem above and type in the result. e.g. for 1+1, type 2.
The content of this field is kept private and will not be shown publicly.
  • Lines and paragraphs break automatically.

More information about formatting options