Oracle监控异常的表设计

--普通索引
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';
查找未建分区的大表

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;

--分区超过100个的
select owner,table_name,partitioning_type,subpartitioning_type from dba_part_tables
where partition_count>=100;

select owner,table_name,num_rows from dba_tab_partitions 
where table_name='RANGE_PART_TAB' order by num_rows desc;

select owner,table_name,last_analyzed,num_rows,blocks from dba_tables
where temporary='Y' and last_analyzed is not null;

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