索引清理

1、查询数据库表空间使用率

select q2.other_tname ts_name, pieces, ts_size ts_size,nvl(largest_chunk,0) largestpc, nvl(total_free,0) totalfree,
nvl(round((total_free/ts_size)*100,2),0) pct_free,ts_size-total_free whatsused,
nvl(100-round((total_free/ts_size)*100,2),100) pct_used,decode(nvl(100-round((total_free/ts_size)*100,0),100),
85,'+',86,'+',87,'+',88,'+',89,'++',90,'++',91,'++',92,'++',93,'++',94,'+++',95,'+++',96,'+++',97,'++++',
98,'+++++',99,'+++++',100,'+++++','') problem
from (select dfs.tablespace_name,count(*) pieces,
round(max(dfs.bytes)/1024/1024,2) largest_chunk,
round(sum(dfs.bytes)/1024/1024,2) total_free
from dba_free_space dfs group by tablespace_name) q1,
(select tablespace_name other_tname,
round(sum(ddf2.bytes)/1024/1024,2) ts_size
from dba_data_files ddf2 group by tablespace_name) q2
where q2.other_tname = q1.tablespace_name(+)
order by nvl(100-round((total_free/ts_size)*100,0),100) desc;

 2、查询空间 MARKET_IDX01 下的各个索引使用率

select t.owner,t.segment_name,t.segment_type,partition_name,t.tablespace_name,t.blocks * 8 / 1024 sizem from dba_segments t
where tablespace_name='MARKET_IDX01' order by t.blocks desc;

 

3、重建索引

alter index PK_ESHOP_JF_INTERFACE_LOG rebuild tablespace MARKET_IDX01;

注意：

如果是索引的话，按上面的方法就可以清理了，如果是 LOBSEGMENT 类型，则需求找到该索引对应的表，之后清理表空间。
select * from dba_segments where segment_name = 'SYS_LOB0000115550C00010$$';

select * from dba_lobs where segment_name =  'SYS_LOB0000115550C00010$$' ;