1、重新分析表和索引。

select  'ANALYZE INDEX OWNER.'|| T.segment_name || ' COMPUTE STATISTICS;' from dba_segments t where t.segment_type ='INDEX' AND t.OWNER='OWNER';

select  'ANALYZE TABLE OWNER.'|| T.segment_name || ' COMPUTE STATISTICS;' from dba_segments t where t.segment_type ='TABLE' AND t.OWNER='OWNER';

2、将表、索引占用空间从大到小列出来。

select T.segment_name,T.segment_type,T.BYTES/(1024*1024)  from USER_SEGMENTS T order by T.BYTES DESC;

--表
ALTER TABLE TABLENAME ENABLE ROW MOVEMENT;
ALTER TABLE TABLENAME SHRINK SPACE CASCADE;

--索引
ALTER INDEX INDEXNAME SHRINK SPACE CASCADE;

4、如果是表，则重建索引。

ALTER INDEX IDEXNAME REBUILD;

--表
ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;

--索引
ANALYZE index INDEXNAME COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;