检测下一次扩展将失败的对象

检测下一次扩展将失败的对象

 

select owner,
       segment_name,
       segment_type,
       seg.tablespace_name,
       decode(ext.allocation_type, 'USER', next_extent, ext.min_extlen) next,
       ext.largest_free_extent,
       extents,
       max_extents
  from dba_segments seg,
       (select d.TABLESPACE_NAME,
               nvl(max(f.bytes), 0) largest_free_extent,
               t.allocation_type,
               t.min_extlen
          from dba_free_space f, dba_data_files d, dba_tablespaces t
         where f.tablespace_name(+) = d.tablespace_name
           and d.tablespace_name = t.tablespace_name
         group by d.tablespace_name, t.allocation_type, t.min_extlen) ext
 where seg.tablespace_name = ext.tablespace_name
   and (decode(ext.allocation_type, 'USER', next_extent, ext.min_extlen) >
       ext.largest_free_extent or seg.extents = seg.max_extents);

 

select b.tablespace_name "Tablespace",
       b.segment_type "Type",
       substr(ext.owner || '.' || ext.segment_name, 1, 50) "Object Name",
       To_Char(decode(freespace.Extent_Management,
                      'DICTIONARY',
                      decode(b.extents,
                             1,
                             b.next_extent,
                             ext.bytes * (1 + b.pct_increase / 100)),
                      'LOCAL',
                      decode(freespace.Allocation_Type,
                             'UNIFORM',
                             freespace.INITIAL_EXTENT,
                             'SYSTEM',
                             ext.bytes)) / 1024,
               '9,999,999,999') "Required Extent(K)",
       to_char(freespace.largest / 1024, '9,999,999,999') "MaxAvail K"
  from dba_segments b,
       dba_extents ext,
       (select B.tablespace_name,
               B.Extent_Management,
               B.Allocation_Type,
               B.INITIAL_EXTENT,
               B.NEXT_EXTENT,
               max(A.bytes) largest
          from dba_free_space A, dba_tablespaces B
         Where B.Tablespace_Name = A.Tablespace_Name
           And B.Status = 'ONLINE'
         group by B.tablespace_name,
                  B.Extent_Management,
                  B.Allocation_Type,
                  B.INITIAL_EXTENT,
                  B.NEXT_EXTENT) freespace
 where b.owner = ext.owner
   and b.segment_type = ext.segment_type
   and b.segment_name = ext.segment_name
   and b.tablespace_name = ext.tablespace_name
   and (b.extents - 1) = ext.extent_id
   and b.tablespace_name = freespace.tablespace_name
   and decode(freespace.Extent_Management,
              'DICTIONARY',
              decode(b.extents,
                     1,
                     (b.next_extent),
                     ext.bytes * (1 + b.pct_increase / 100)),
              'LOCAL',
              decode(freespace.Allocation_Type,
                     'UNIFORM',
                     freespace.INITIAL_EXTENT,
                     'SYSTEM',
                     ext.bytes)) > freespace.largest
 order by b.Tablespace_Name, b.Segment_Type, b.Segment_Name