本文共 3917 字，大约阅读时间需要 13 分钟。
1. 当前执行存储过程的用户下建立表MON_SEGMENT_USAGE
CREATE TABLE MON_SEGMENT_USAGE(owner varchar2(20),segment_name varchar2(40),segment_type varchar2(40),total_bytes number(20),full_bytes number(20),pct_used number(10),created_date date);
2. 若表MON_SEGMENT_USAGE已经存在，在执行存过之前最好truncate此表，以免干扰
3. 存储过程的内容，查找碎片率在40%以上的表或索引（即实际使用率在60%以下）
set serveroutput on;
DECLARE
unf number(20);
unfb number(20);
fs1 number(20);
fs1b number(20);
fs2 number(20);
fs2b number(20);
fs3 number(20);
fs3b number(20);
fs4 number(20);
fs4b number(20);
full number(20);
fullb number(20);
pct number(10);
v_cnt number(10);
v_str varchar2(200);
v_owner varchar2(200);--owner要加单引号
v_segment_type varchar2(200);
--segment_type要加单引号，可以是'TABLE','INDEX',分区表也可以使用dbms_space.space_usage，不过需要指定特---定分区partition_name
begin
v_cnt :=0;
v_owner := &owner;
v_segment_type :=&segment_type;
for my_record in ( select * from (select owner,segment_name,segment_type,sum(bytes/1024/1024) from dba_segments where segment_type=v_segment_type and owner =v_owner group by segment_type,owner,segment_name order by sum(bytes/1024/1024) desc) where rownum<10 )loop
dbms_space.space_usage(my_record.owner,my_record.segment_name,my_record.SEGMENT_TYPE,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
if nvl(FS1B,0)+nvl(FS2B,0)+nvl(FS3B,0)+nvl(FS4B,0) >0 then
pct:=trunc(nvl(FULLB,0)*100/(nvl(FULLB,0)+nvl(FS1B,0)+nvl(FS2B,0)+nvl(FS3B,0)+nvl(FS4B,0)),2);
if pct <60 then
INSERT INTO MON_SEGMENT_USAGE VALUES(my_record.OWNER,my_record.SEGMENT_NAME,my_record.SEGMENT_TYPE,nvl(FULLB,0)+nvl(FS1B,0)+nvl(FS2B,0)+nvl(FS3B,0)+nvl(FS4B,0),nvl(FULLB,0),PCT,SYSDATE);
end if;
end if;
end loop;
end;
4. 表执行结果演示
SQL> select count(*) from TEST_STAT;
COUNT(*)
----------
9979947
SQL> delete from TEST_STAT;
9979947 rows deleted.
SQL> commit;
SQL> select count(*) from TEST_STAT;
COUNT(*)
----------
0
执行过上述存过之后，空间没释放，此表被插入 MON_SEGMENT_USAGE中
SQL> select * from MON_SEGMENT_USAGE;
OWNER SEGMENT_NAME SEGMENT_TYPE TOTAL_BYTES FULL_BYTES PCT_USED CREATED_D
-------------------- ---------------------------------------- ---------------------------------------- ----------- ---------- ---------- ---------
TEST TEST_STAT TABLE 321757184 0 0 12-FEB-20
SQL> select bytes from dba_segments where segment_name='TEST_STAT';
BYTES
----------
327155712
5. 索引执行结果演示
6. 分区表可以使用dbms_space.space_usage，但是需要指定partition_name,这里未做具体研究
dbms_space.space_usage(my_record.owner,my_record.segment_name,my_record.partition_name,my_record.SEGMENT_TYPE,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
7. 对于表的碎片，可以通过shrink解决；索引碎片可以重建索引；分区表碎片可以参考如下
（1）表move
select table_NAME,HIGH_VALUE,TABLESPACE_NAME,PARTITION_NAME from user_tab_partitions;
select 'alter table '||table_name||' move partition '||partition_name||' tablespace users;' from user_tab_partitions where table_name='TEST_PARTITION';
alter table TEST_PARTITION move partition P1 tablespace users;
alter table TEST_PARTITION move partition P2 tablespace users;
alter table TEST_PARTITION move partition P3 tablespace users;
alter table TEST_PARTITION move partition P4 tablespace users;
select table_NAME,HIGH_VALUE,TABLESPACE_NAME,PARTITION_NAME from user_tab_partitions;
（2）重建索引：
此时若分区表有索引，分区索引UNUSABLE，需重建索引
select TABLESPACE_NAME,INDEX_NAME,PARTITION_NAME,status from user_ind_partitions;
select TABLE_NAME,INDEX_NAME ,LOCALITY from user_part_indexes where TABLE_NAME='TEST_PARTITION';
IDX_TEST_PARTITION
select 'alter index '||index_name||' rebuild partition '||partition_name||' tablespace users;' from user_ind_partitions where index_name='IDX_TEST_PARTITION' and status='UNUSABLE';
alter index IDX_TEST_PARTITION rebuild partition P1 tablespace users;
alter index IDX_TEST_PARTITION rebuild partition P2 tablespace users;
alter index IDX_TEST_PARTITION rebuild partition P3 tablespace users;
alter index IDX_TEST_PARTITION rebuild partition P4 tablespace users;
select TABLESPACE_NAME,INDEX_NAME,PARTITION_NAME,status from user_ind_partitions;
转载地址：http://kmwmf.baihongyu.com/