查询并清理紧张的表空间

1.通过下面sql，查询比较紧张的表空间
SELECT ts.tablespace_name,
            NVL(ROUND (total_d.total_bytes/1024/ 1024,0 ),0)total_m,
            NVL(ROUND (free_d.free_bytes/1024/ 1024,0 ),0)free_m,
             ROUND (NVL(free_d.free_bytes,0 )/(NVL(total_d.total_bytes, 0)+0.000001 ),4)* 100  pct_free
     FROM (SELECT tablespace_name,SUM(bytes) total_bytes
            FROM dba_data_files ddf
          GROUP BY ddf.tablespace_name) total_d,
          ( SELECT  tablespace_name,SUM (bytes) free_bytes
            FROM dba_free_space dfs
          GROUP BY dfs.tablespace_name) free_d,
          dba_tablespaces ts
    WHERE ts.tablespace_name=total_d.tablespace_name(+)
    AND ts.tablespace_name=free_d.tablespace_name(+)
    AND ts.Contents<>'TEMPORARY'
  ORDER BY ROUND (NVL(free_d.free_bytes,0 )/(NVL(total_d.total_bytes, 0)+0.000001 ),4)* 100 asc
 ;
 

2.找出表空间中比较大的索引
select t.owner,
       t.segment_name,
       t.segment_type,
       t.tablespace_name,
      -- t.BYTES
       sum(t.bytes) / 1024 / 1024 / 1024 GB
  from dba_segments t
 where t.tablespace_name = 'XXX'
   and t.segment_type = 'INDEX'
   group by t.owner,t.segment_name,t.segment_type,t.tablespace_name
   order by sum (t.bytes) / 1024 / 1024 / 1024 desc ;
;

3.重建索引
--alter index XXX rebuild tablespace tbs_name;

alter index yyy.xxx rebuild;

4.如果重建索引效果不大，可以将表迁移到别的表空间，例如
 alter TABLE  HR.PER_IMAGES  MOVE tablespace APPS_TS_MEDIA ;
 
select * from dba_indexes t where upper(t.table_name)='PER_IMAGES' ;
alter index hr.PER_IMAGES_PK rebuild ;
alter index hr.PER_IMAGES_UK2 rebuild ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23169974/viewspace-754732/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23169974/viewspace-754732/