统计表已用区间占段可用区间量的比.sql

介绍oracle dba常用的报表代码,已经异常查找等

---统计表已用区间占段可用区间量的比(包括索引)
select T.Owner,
       T.Table_Name,
       S.Segment_Type,
       T.Blocks Used,
       T.Empty_Blocks Empty,
       S.Blocks Allocated,
       T.Blocks / S.Blocks Pctusd
  from DBA_TABLES T, DBA_SEGMENTS S
 where T.Owner = S.Owner
   and T.Table_Name = S.Segment_Name
   and S.Segment_Type = 'TABLE'
   and T.Blocks / S.Blocks > 0.95;
union
select I.Owner,
       I.Index_Name,
       S.Segment_Type,
       I.Leaf_Blocks Used,
       S.Blocks - 1 - I.Leaf_Blocks Empty,
       S.Blocks Allocated,
       I.Leaf_Blocks / S.Blocks Pctusd
  from DBA_INDEXES I, DBA_SEGMENTS S
 where I.Owner = S.Owner
   and I.Index_Name = S.Segment_Name
   and S.Segment_Type = 'INDEX'
   and I.Leaf_Blocks / S.Blocks > 0.95
---统计表空间的段的下一个扩展比该表空间的自由空间的包含最大区间还要大
select Owner, Segment_Name, Segment_Type
from DBA_SEGMENTS
where Next_Extent >
(select MAX(Bytes) from DBA_FREE_SPACE
where Tablespace_Name = DBA_SEGMENTS.Tablespace_Name);