> 参考 《收获不止SQL优化》

##### 监控失效分区索引
``` sql
--普通索引
select t.owner,t.index_name,t.table_name,t.blevel,t.num_rows,t.leaf_blocks,t.distinct_keys
from dba_indexes t where t.status='INVALID';

--分区索引
select t2.owner,t1.blevel,t.leaf_blocks,t1.index_name,t2.table_name,t1.partition_name,t1.status
from dba_ind_partitions t1,dba_indexes t2
where t1.index_name=t2.index_name and t1.owner=t2.owner
and t1.status='UNUSABLE';
```

##### 查找未建分区的大表
``` sql
select owner,segment_name,segment_type,sum(bytes/1024/1024/1024) GB from dba_segments
where segment_type='TABLE' 
group by segment_name,segment_type having sum(bytes/1024/1024/1024) >=10
order by GB desc;
```

##### 检查分区数过多的表
``` sql
--分区超过100个的
select owner,table_name,partitioning_type,subpartitioning_type from dba_part_tables
where partition_count>=100;
```

##### 检查分区表大小严重不均衡的
``` sql
select owner,table_name,num_rows from dba_tab_partitions 
where table_name='RANGE_PART_TAB' order by num_rows desc;
```

##### 检查哪些全局临时表被收集统计信息
``` sql
select owner,table_name,last_analyzed,num_rows,blocks from dba_tables
where temporary='Y' and last_analyzed is not null;
```

##### 检查表中有没有过时的类型字段
``` sql

select owner,table_name,column_name,data_type 
from dba_tab_columns
where data_type in('LONG','CHAR');
```

ITPub博客

【TABLE】Oracle监控异常的表设计

最新文章