整理oracle数据表索引碎片语句,oracle 表碎片整理

又是一年双11，双十一对从事电商的it人员来说是一场噩梦，这个只是前奏，下面说重点：

表碎片整理,首先收集那些表需要做碎片整理：

1.1根据统计信息检查表碎片：

SELECT table_name,

ROUND ( (blocks 8), 2) "高水位空间 k",

ROUND ( (num_rows avg_row_len / 1024), 2) "真实使用空间 k",

ROUND ( (blocks 10 / 100) 8, 2) "预留空间(pctfree) k",

ROUND ( ( blocks 8 - (num_rows avg_row_len / 1024) - blocks 8 10 / 100), 2) "浪费空间 k"

FROM user_tables

WHERE temporary = 'N'

ORDER BY 5 DESC;

1.2. 是和业务开发人员沟通那些主要的业务表做了大量的delete、update操作，确定要整理的表范围。

2.1.下面是碎片整理步骤：

alter table app_info enable row movement; --打开行移动

alter table app_info shrink space cascade; --压缩表及相关数据段并下调HWM (此步骤会影响业务)

alter table app_info shrink space compact; --只压缩不下调HWM

alter table app_info shrink space ; --下调HWM (此步骤会影响业务)

alter table app_info disable row movement; --关闭行移动

其中alter table app_info shrink space compact; alter table app_info shrink space ; 两个步骤等于alter table app_info shrink space cascade; 操作

注意：

IOT索引组织表、用rowid创建的物化视图的基表、带有函数索引的表、SECUREFILE 大对象、压缩表不能使用Shrink 操作。

3.1 整理完碎片后最好重新收集统计信息：

begin

dbms_stats.gather_table_stats(ownname => 'chunqiu',tabname => 'app_info',cascade => true);

end;

4.1下面为写在plsql语句块中的参考，为下面脚本准备：

begin

EXECUTE IMMEDIATE 'alter table app_info shrink space ';

EXECUTE IMMEDIATE 'alter table app_info disable row movement ';

end;

5.1如果表很多怎么办？，特备是最后的下调高水位线基本上都需要在晚上业务低峰期操作，甚至有的会申请挂免战牌，下面写个脚本批量处理加上定时任务，可以让dba们节约时间好好休息下：

create table T_TABALE

(

table_name VARCHAR2(200) not null,

compact_status NUMBER default 0 not null,

shrink_status NUMBER default 0 not null

)；

alter table T_TABALE add constraint PK_T_TABALE primary key (TABLE_NAME)；

把要整理的表名字插入到该表。

5.1.先开启row movement：

BEGIN

FOR i IN (select table_name from T_TABALE ) LOOP

EXECUTE IMMEDIATE 'alter table ' || i.table_name ||' enable row movement ' ;

END LOOP;

END;

5.2.整理碎片：

BEGIN

FOR i IN (select table_name from T_TABALE where compact_status = 0 ) LOOP

begin

EXECUTE IMMEDIATE 'alter table ' || i.table_name ||' shrink space compact ' ;

update T_TABALE set status = 1 where table_name = i.table_name ;

commit;

EXCEPTION WHEN OTHERS THEN null;

END;

END LOOP;

END;

5.3.降低高水位，步骤最好结合定时任务放在晚上执行：

这个可以写个定时任务，晚上执行

BEGIN

FOR i IN (select table_name from T_TABALE where compact_status = 0 ) LOOP

begin

EXECUTE IMMEDIATE 'alter table ' || i.table_name ||' shrink space ' ;

update T_TABALE set shrink_status = 1 where table_name = i.table_name ;

commit;

EXCEPTION WHEN OTHERS THEN null;

END;

END LOOP;

END;

5.5 关闭row movement：

BEGIN

FOR i IN (select table_name from T_TABALE ) LOOP

BEGIN

EXECUTE IMMEDIATE 'alter table ' || i.table_name ||' disable row movement ' ;

END LOOP;

END;

6.1 最后别忘了收集下统计信息，收集统计信息的批量脚本自己实现吧。

突然想起来了，供参考：

BEGIN

FOR i IN (select blocks*8/1024/1024 ,table_name from dba_tables where table_name in(select table_name from pacs.T_TABALE where status = 11 ) order by 1 ) LOOP

begin

EXECUTE IMMEDIATE 'begin dbms_stats.gather_table_stats(ownname =>' || '''pacs'''|| ', tabname => ''' || i.table_name || '''' || ' ,cascade => true) ; end; ' ;

update pacs.T_TABALE set status = 12 where table_name = i.table_name ;

commit;

-- EXCEPTION WHEN OTHERS THEN null;

END;

END LOOP;

END;