转 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;

上一篇:ORACLE ORA-01950 对表空间无权限 但我本来就赋给了用户对应表空间权限


下一篇:SQL 数据库学习之路-转自大神笔记