分区表碎片整理（move）

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;

•