shrink回收分区表碎片

实验如下：

CREATE TABLE "SCOTT"."T4"
   (    "A" NUMBER,
        "B" NUMBER
   )
  PARTITION BY RANGE ("A")
(PARTITION "PART1"  VALUES LESS THAN (10),
PARTITION "PART2"  VALUES LESS THAN (20) ) ;

begin                                    
for v1 in 1..19
loop
insert into scott.t4 values(v1,dbms_random.value(1,100000));
commit;
end loop;
end;
/
INSERT INTO scott.T4 SELECT * FROM sT4;

SQL>   CREATE TABLE "SCOTT"."T4"
   (    "A" NUMBER,
        "B" NUMBER
  2    3    4     )
  5    PARTITION BY RANGE ("A")
  6  (PARTITION "PART1"  VALUES LESS THAN (10),
  7  PARTITION "PART2"  VALUES LESS THAN (20) ) ;

Table created.

SQL> begin                                    
  2  for v1 in 1..19
  3  loop
  4  insert into scott.t4 values(v1,dbms_random.value(1,100000));
  5  commit;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>

SQL> conn scott/tiger;
Connected.
SQL> INSERT INTO T4 SELECT * FROM T4;

19 rows created.

省略.........

SQL> INSERT INTO T4 SELECT * FROM T4;

77824 rows created.

SQL> INSERT INTO T4 SELECT * FROM T4;

155648 rows created.

SQL> INSERT INTO T4 SELECT * FROM T4;

311296 rows created.

SQL> commit;

Commit complete.

SQL> commit;

Commit complete.

SQL> select count(*) from t4;

  COUNT(*)
----------
    622592

SQL>

--sys用户执行查询表大小及碎片
SQL> col SEGMENT_NAME for a15
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';

OWNER                          SEGMENT_NAME    PARTITION_NAME                 SEGMENT_TYPE           SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT                          T4              PART1                          TABLE PARTITION            16
SCOTT                          T4              PART2                          TABLE PARTITION            16

SQL>
SQL> set lines 200
SQL> select table_name,
  2         num_rows,
  3         avg_row_len * num_rows / 1024 / 1024 actual_mb,
  4         blocks * 8 / 1024 high_water_mb,
  5         (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
  6    from dba_tables
  7   where table_name = '&table_name' and owner='&owner';
Enter value for table_name: T4
Enter value for owner: SCOTT
old   7:  where table_name = '&table_name' and owner='&owner'
new   7:  where table_name = 'T4' and owner='SCOTT'

TABLE_NAME                       NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4

SQL>

--分析表：
SQL> analyze table scott.T4 compute statistics;

Table analyzed.

SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';

OWNER                          SEGMENT_NAME    PARTITION_NAME                 SEGMENT_TYPE           SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT                          T4              PART1                          TABLE PARTITION            16
SCOTT                          T4              PART2                          TABLE PARTITION            16

SQL> select table_name,
  2         num_rows,
  3         avg_row_len * num_rows / 1024 / 1024 actual_mb,
  4         blocks * 8 / 1024 high_water_mb,
  5         (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
  6    from dba_tables where table_name='T4';

TABLE_NAME                       NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4                                 622592     16.625      31.46875         14.84375

SQL>

--删除表数据：
SQL> delete scott.t4 where rownum < 600000;
 
599999 rows deleted.

SQL>

--再查看表发小及碎片情况：
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';

OWNER                          SEGMENT_NAME    PARTITION_NAME                 SEGMENT_TYPE           SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT                          T4              PART1                          TABLE PARTITION            16
SCOTT                          T4              PART2                          TABLE PARTITION            16

SQL>
SQL>
SQL>  select table_name,
  2           num_rows,
  3           avg_row_len * num_rows / 1024 / 1024 actual_mb,
  4           blocks * 8 / 1024 high_water_mb,
  5           (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
  6      from dba_tables where table_name='T4';

TABLE_NAME                       NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4                                 622592     16.625      31.46875         14.84375

SQL>


--再次分析一下表
SQL>  analyze table scott.T4 compute statistics;

Table analyzed.

SQL>
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';

OWNER                          SEGMENT_NAME    PARTITION_NAME                 SEGMENT_TYPE           SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT                          T4              PART1                          TABLE PARTITION            16
SCOTT                          T4              PART2                          TABLE PARTITION            16

SQL>  select table_name,
  2           num_rows,
  3           avg_row_len * num_rows / 1024 / 1024 actual_mb,
  4           blocks * 8 / 1024 high_water_mb,
  5           (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
  6      from dba_tables where table_name='T4';

TABLE_NAME                       NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4                                  22593 .603298187      31.46875       30.8654518

SQL>

发现使劲的表大小已经变为0.603298187MB了.

--使用dbms_stat包分析分区表，如下：
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SCOTT',TABNAME=> 'T4',METHOD_OPT=> 'for all indexed columns size auto',CASCADE=> TRUE,ESTIMATE_PERCENT=> 100);

PL/SQL procedure successfully completed.

SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';

OWNER                          SEGMENT_NAME    PARTITION_NAME                 SEGMENT_TYPE           SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT                          T4              PART1                          TABLE PARTITION            16
SCOTT                          T4              PART2                          TABLE PARTITION            16

SQL>  select table_name,
  2           num_rows,
  3           avg_row_len * num_rows / 1024 / 1024 actual_mb,
  4           blocks * 8 / 1024 high_water_mb,
  5           (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
  6      from dba_tables where table_name='T4';

TABLE_NAME                       NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4                                  22593 .538659096      31.46875       30.9300909

结果同上。

--查询每个分区的碎片情况；
SQL> select t3.table_name, t3.partition_name,t3.num_rows * t3.AVG_ROW_LEN / 1024 / 1024 actual_mb,T3.blocks * 8 / 1024 high_water_mb,(blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 ) need_recovery_mb,t3.empty_blocks,t3.last_analyzed from dba_tab_partitions t3 where t3.table_name ='T4';

TABLE_NAME                     PARTITION_NAME                  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB EMPTY_BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ------------- ---------------- ------------ -------------------
T4                             PART1                                   0     15.734375        15.734375           34 2017-10-18 06:06:49
T4                             PART2                          .538659096     15.734375       15.1957159           34 2017-10-18 06:06:49

SQL>  



--进行shrink表t4（整个分区shrink，当然也可以针对子分区shrink,例如alter table PTABLE MODIFY PARTITION PTABLE_P2 shrink space）

SQL> alter table scott.t4 enable row movement;

Table altered.

SQL>
SQL> alter table scott.t4 shrink space cascade;

Table altered.

SQL> alter table scott.t4 disable row movement;

Table altered.


--查看表大小及碎片情况：
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';

OWNER                          SEGMENT_NAME    PARTITION_NAME                 SEGMENT_TYPE           SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT                          T4              PART1                          TABLE PARTITION         .1875
SCOTT                          T4              PART2                          TABLE PARTITION         .8125

SQL>
SQL>  select table_name,
  2           num_rows,
  3           avg_row_len * num_rows / 1024 / 1024 actual_mb,
  4           blocks * 8 / 1024 high_water_mb,
  5           (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
  6      from dba_tables where table_name='T4';

TABLE_NAME                       NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4                                  22593 .538659096      31.46875       30.9300909

SQL>

--再次收集统计信息：
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SCOTT',TABNAME=> 'T4',METHOD_OPT=> 'for all indexed columns size auto',CASCADE=> TRUE,ESTIMATE_PERCENT=> 100);

PL/SQL procedure successfully completed.

SQL>
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';

OWNER                          SEGMENT_NAME    PARTITION_NAME                 SEGMENT_TYPE           SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT                          T4              PART1                          TABLE PARTITION         .1875
SCOTT                          T4              PART2                          TABLE PARTITION         .8125

SQL>  select table_name,
  2           num_rows,
  3           avg_row_len * num_rows / 1024 / 1024 actual_mb,
  4           blocks * 8 / 1024 high_water_mb,
  5           (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
  6      from dba_tables where table_name='T4';

TABLE_NAME                       NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4                                  22593 .538659096      .6640625       .125403404

--同时查看各子分区情况：
SQL> select t3.table_name, t3.partition_name,t3.num_rows * t3.AVG_ROW_LEN / 1024 / 1024 actual_mb,T3.blocks * 8 / 1024 high_water_mb,(blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 ) need_recovery_mb,t3.empty_blocks,t3.last_analyzed from dba_tab_partitions t3 where t3.table_name ='T4';

TABLE_NAME                     PARTITION_NAME                  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB EMPTY_BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ------------- ---------------- ------------ -------------------
T4                             PART1                                   0      .0078125         .0078125           34 2017-10-18 06:15:37
T4                             PART2                          .538659096        .65625       .117590904           34 2017-10-18 06:15:37

注意：即碎片回收后续收集统计信息，碎片记录才会消失
完成！！！！！！！！！！！！1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2147402/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2147402/