Faster DBA_SEGMENTS

As we get more and more diverse types of segments within Oracle, the DBA_SEGMENTS view has become more complex (and slower and s l o w e r). Typically we're only interested in 'normal' segments, that is, we often use other means to monitor temporary and rollback segments.

A little probing into the internals lets us take advantage of this to give a much faster view (8i only) definition for use in regular monitoring scripts.

create view sys.quick_segs (
 OWNER        ,
 SEGMENT_NAME ,
 PARTITION_NAME,
 SEGMENT_TYPE   ,
 TABLESPACE_NAME,
 BYTES          ,
 BLOCKS         ,
 EXTENTS        ,
 INITIAL_EXTENT ,
 NEXT_EXTENT    ,
 MIN_EXTENTS    ,
 MAX_EXTENTS    ,
 PCT_INCREASE   ,
 BUFFER_POOL   ) as
select u.name, o.name, o.subname, so.object_type, ts.name,
       dbms_space_admin.segment_number_blocks(ts.ts#, s.file#,
       s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
       o.dataobj#, s.blocks)*ts.blocksize,
       dbms_space_admin.segment_number_blocks(ts.ts#, s.file#,
       s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
       o.dataobj#, s.blocks),
       dbms_space_admin.segment_number_extents(ts.ts#, s.file#,
       s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
       o.dataobj#, s.extents),
       s.iniexts * ts.blocksize,
       decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize),
       s.minexts, s.maxexts,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),s.extpct),
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from sys.user$ u, sys.obj$ o, sys.ts$ ts, 
     sys.sys_objects so, sys.seg$ s, sys.file$ f
where s.file# = so.header_file
  and s.block# = so.header_block
  and s.ts# = so.ts_number
  and s.ts# = ts.ts#
  and o.obj# = so.object_id
  and o.owner# = u.user#
  and s.type# = so.segment_type_id
  and o.type# = so.object_type_id
  and s.ts# = f.ts#
  and s.file# = f.relfile#