脚本说明
目前网上较多的是show_space函数收集单个表的碎片情况，但是实际工作中需要定期对表碎片进行收集，于是编写了自动收集数据库表碎片的存储过程。
ENMO_FRAGMENTATION_RATE --表碎片信息汇总表
p_enmo_list_fragmentation --收集数据库表碎片的存储过程
p_enmo_list_fragmentation_err–报错表的信息
通过执行
exec p_enmo_list_fragmentation(owner); 自动将数据库表的碎片情况汇总到ENMO_FRAGMENTATION_RATE，如果中间有中断的情况，会自动跳过已收集的碎片。如果需要收集多个用户，重复执行exec p_enmo_list_fragmentation(owner);脚本即可
按用户检查所有表碎片
– Create table
create table ENMO_FRAGMENTATION_RATE
(
table_owner VARCHAR2(256),
table_name VARCHAR2(256),
table_part_name VARCHAR2(256),
total_byte NUMBER,
act_byte NUMBER,
rate NUMBER,
flag NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create table ENMO_FRAGMENTATION_RATE_err
(
table_owner VARCHAR2(256),
table_name VARCHAR2(256),
err_measge VARCHAR2(500)
)
tablespace USERS
– Create PROCEDURE
CREATE OR REPLACE PROCEDURE p_enmo_list_Fragmentation(p_owner varchar2) AUTHID CURRENT_USER AS
p_segname VARCHAR2(100);
p_type VARCHAR2(30);
p_partition VARCHAR2(50);
l_unformatted_blocks NUMBER;
l_unformatted_bytes NUMBER;
l_fs1_blocks NUMBER;
l_fs1_bytes NUMBER;
l_fs2_blocks NUMBER;
l_fs2_bytes NUMBER;
l_fs3_blocks NUMBER;
l_fs3_bytes NUMBER;
l_fs4_blocks NUMBER;
l_fs4_bytes NUMBER;
l_full_blocks NUMBER;
l_full_bytes NUMBER;
l_total_blocks NUMBER;
l_total_bytes NUMBER;
l_unused_blocks NUMBER;
l_unused_bytes NUMBER;
l_LastUsedExtFileId NUMBER;
l_LastUsedExtBlockId NUMBER;
l_LAST_USED_BLOCK NUMBER;
v_Fragmentation_rate number;
v_count number;
o_err varchar2(2000);
BEGIN
for x in (select t1.owner,
t1.segment_name,
t1.segment_type,
t1.partition_name
from Dba_SEGMENTs t1
WHERE OWNER = p_owner and segment_type like ‘TABLE%’ and segment_name not like ‘BIN$%’) loop
begin
p_segname := UPPER(x.segment_name);
p_type := x.segment_type;
p_partition := x.partition_name;
if (nvl(p_partition, ‘1’) = ‘1’) then
select count()
INTO V_COUNT
from ENMO_fragmentation_rate t
where t.table_owner = x.owner
and t.table_name = x.segment_name
and flag = ‘1’;
else
select count()
INTO V_COUNT
from ENMO_fragmentation_rate t
where t.table_owner = x.owner
and t.table_name = x.segment_name
and t.table_part_name = x.partition_name
and flag = ‘1’;
end if;
IF (V_COUNT = 0) THEN
DBMS_SPACE.UNUSED_SPACE(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK);
DBMS_SPACE.SPACE_USAGE(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
v_Fragmentation_rate := round((l_total_bytes - l_full_bytes) /
l_total_bytes,
2) * 100;
insert into ENMO_fragmentation_rate
values
(x.owner,
x.segment_name,
x.partition_name,
l_total_bytes,
l_full_bytes,
v_Fragmentation_rate,
to_number(‘1’));
commit;
END IF;
exception
when others THEN
o_err:=SQLERRM;
insert into ENMO_FRAGMENTATION_RATE_err
values
(x.owner,
x.segment_name,
o_err);
commit;
null;
end;
end loop;
END;
/
–执行样例：
exec p_enmo_list_Fragmentation(‘HZQ_MIN’) ;
select * from enmo_Fragmentation_rate;
可以选择性的对enmo_Fragmentation_rate内的表定期碎片整理。
