监控失效分区索引
代码语言：javascript复制--普通索引
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';
查找未建分区的大表查找未建分区的大表
代码语言：javascript复制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;
检查分区数过多的表
代码语言：javascript复制--分区超过100个的
select owner,table_name,partitioning_type,subpartitioning_type from dba_part_tables
where partition_count>=100;
检查分区表大小严重不均衡的
代码语言：javascript复制select owner,table_name,num_rows from dba_tab_partitions
where table_name='RANGE_PART_TAB' order by num_rows desc;
检查哪些全局临时表被收集统计信息
代码语言：javascript复制select owner,table_name,last_analyzed,num_rows,blocks from dba_tables
where temporary='Y' and last_analyzed is not null;
检查表中有没有过时的类型字段
代码语言：javascript复制select owner,table_name,column_name,data_type
from dba_tab_columns
where data_type in('LONG','CHAR');
