检查每一个在其指定的最大扩展倍数内的段.sql

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

----查询远程数据库以检查每一个在其指定的最大扩展倍数内的段。它
----通过数据库链接访问那些数据库，并且链接名被用作输出文件名的一部分。倍数值应该总大
----于1—当实际盘区数与最大盘区数进行比较时应该乘以这个值。如为了确定哪些段达到了它们
----的最大扩展的8 0 %，应把这个倍数值设为1 . 2。
select Owner, /*owner of segment*/
       Segment_Name, /*name of segment*/
       Segment_Type, /*type of segment*/
       Extents, /*number of extents already acquired*/
       Blocks /*number of blocks already acquired*/
  from DBA_SEGMENTS@ &&1 s
 where /*for cluster segments*/
 (S.Segment_Type = 'CLUSTER' and exists
  (select 'x'
     from DBA_CLUSTERS@ &&1 c
    where C.Owner = S.Owner
      and C.Cluster_Name = S.Segment_Name
      and C.Max_Extents < S.Extents * &&2))
 or /*for table segments*/
 (s.segment_type = 'TABLE' and exists
  (select 'x'
     from DBA_TABLES@ &&1 t 
    where T.Owner = S.Owner
      and T.Table_Name = S.Segment_Name
      and T.Max_Extents < S.Extents * &&2))
 or /*for index segments*/
 (S.Segment_Type = 'INDEX' and exists
  (select 'x'
     from DBA_INDEXES@ &&1 i
    where I.Owner = S.Owner
      and I.Index_Name = S.Segment_Name
      and I.Max_Extents < S.Extents * &&2))
 or /*for rollback segments*/
 (S.Segment_Type = 'ROLLBACK' and exists
  (select 'x'
     from DBA_ROLLBACK_SEGS@ &&1 r
    where R.Owner = S.Owner
      and R.Segment_Name = S.Segment_Name
      and R.Max_Extents < S.Extents * &&2))
 order by 1, 2 ;
 ----------------------
 --期查询数据库以确定是否还有其他段不能扩展到表
 --空间的可用空间中。
 select owner, segment_name, segment_type
  from dba_segments
 where next_extent >
       (select sum(bytes)
          from dba_free_space
         where tablespace_name = dba_segments.tablespace_name)