转 OracLe 数据清理

sq1:
检查是否是 分区表
select table_owner,table_name,partition_name from dba_tab_partitions where table_owner='OSTA'；

sql2:
核对分区：
select table_name,t.partition_name,SUBPARTITION_COUNT,T.blocks*8192/1024/1024/1024 size_gb from dba_tab_partitions t
where t.table_name in(
'_END')
order by 1;

核对分区数量
select table_name,count(*) from dba_tab_partitions t
where t.table_name in (
'HIST',
'group by table_name
order by 2;

3.核对索引类型：
##判断索引是否失效
select owner,table_name,index_name,i.index_type,i.partitioned,i.uniqueness,status from dba_indexes i where i.status='UNUSABLE';

##判断分区索引是否 local 还是 gloable,有值返回就是local, 没有值返回就是gloable
select locality,ALIGNMENT,index_name,table_name from dba_part_indexes i where i.table_name in
(
'E00');

##判断分表上所有的索引
select owner,table_name,index_name,i.index_type,i.partitioned,i.uniqueness,status from dba_indexes i
where i.table_name in
(
'E002_R')

 

#####建议修改索引类型为

举例如下，可以考虑建立成local 类型的分区索引，而不是默认全局索引。
CREATE INDEX local_one ON employees (employee_id) LOCAL;