ORACLE 收缩段（移动HWM）

Q132284591

今天一客户打电话，说对他们的表删除了很多行，但是查看表所站的块没有减少。这是因为HWM没有移动的原因。HWM如最高水位线一样，默认只有增加。
    HWM对数据库的性能影响是很大的：
    1，比如做全表扫描的时候。
    2，在insert语句中增加了append提示的时候。
    降低HWM有很多的方法。
    1，exp/imp表。
    2，create table  as select ....和alter table .. rename ...
    3, move 表。
    4，shrink表（10G）
    下面我们介绍move与shrink两种方法。
    move与shrink他们能移动HWM,还能消除部分行迁移，消除空间碎片。
    下面是move与shrink的操作。
    创建测试数据：　　

• SQL> create user luoping identified by htz;
  
• 
  
• User created.
  
• 
  
• SQL> grant connect,resource to luoping;
  
• 
  
• Grant succeeded.
  
• 
  
• SQL> conn luoping/htz
  
• Connected.
  
• 
  
• SQL> create table htz1 (a int)
  
•   2  ;
  
• 
  
• Table created.
  
• 
  
• SQL> create table htz2 (a int);
  
• 
  
• Table created.
  
• 
  
• SQL> create index htz1_a on htz1(a);
  
• 
  
• Index created.
  
• 
  
• SQL> create index htz2_a on htz2(a);
  
• 
  
• Index created.
  
• 
  
• 
  
• begin
  
• for i in 1..1000000 loop
  
• insert into htz2 values(i);
  
• insert into htz1 values(i);
  
• if mod(i,1000)=0 then
  
• commit;
  
• end if;
  
• end loop;
  
• end;
  
• /
  
• 
  
• SQL> begin
  
• for i in 1..1000000 loop
  
•   2    3  insert into htz2 values(i);
  
• insert into htz1 values(i);
  
•   4    5  if mod(i,1000)=0 then
  
•   6  commit;
  
•   7  end if;
  
•   8  end loop;
  
•   9  end;
  
• 10  /
  
• PL/SQL procedure successfully completed.
  

　　

　　查看表与索引的消息
　　

• oracle@oracle10grac1 ~]$ dbtable orcl1 htz1
  
• 
  
• OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE)
  
• ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ----------------
  
• LUOPING    HTZ1       USERS                                        N DEFAULT             1 NO  N    12M                10                                     10
  
• 
  
• OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST
  
• ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ----------
  
• LUOPING    htz1       USERS           HTZ1_A                                          VALID    DEFAULT    NORMAL                A                        1
  
• [oracle@oracle10grac1 ~]$ dbtable orcl1 htz1
  
• 
  
• OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE)
  
• ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ----------------
  
• LUOPING    HTZ1       USERS                                        N DEFAULT             1 NO  N    13M                10                                     10
  
• 
  
• OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST
  
• ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ----------
  
• LUOPING    htz1       USERS           HTZ1_A                                          VALID    DEFAULT    NORMAL                A                        1
  
• [oracle@oracle10grac1 ~]$ dbtable orcl1 htz2
  
• 
  
• OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE)
  
• ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ----------------
  
• LUOPING    HTZ2       USERS                                        N DEFAULT             1 NO  N    13M                10                                     10
  
• 
  
• OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST
  
• ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ----------
  
• LUOPING    htz2       USERS           HTZ2_A                                          VALID    DEFAULT    NORMAL                A                        1
  

　　

　　对表进行分析：
　　

• [oracle@oracle10grac1 ~]$ dbmsstats orcl1 luoping htz1
  
• [oracle@oracle10grac1 ~]$ dbmsstats orcl1 luoping htz2
  
• 
  
• [oracle@oracle10grac1 ~]$ dbtable orcl1 htz2
  
• 
  
• OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE)
  
• ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ----------------
  
• LUOPING    HTZ2       USERS      2012-05-30 23:36:00      1630     N DEFAULT             1 NO  N    13M                10               1001889               10
  
• 
  
• OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST
  
• ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ----------
  
• LUOPING    htz2       USERS           HTZ2_A          2012-05-30 23:36:10        3437 VALID    DEFAULT    NORMAL        1000000 A                        1
  
• [oracle@oracle10grac1 ~]$ dbtable orcl1 htz1
  
• 
  
• OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE)
  
• ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ----------------
  
• LUOPING    HTZ1       USERS      2012-05-30 23:35:04      1630     N DEFAULT             1 NO  N    13M                10                999943               10
  
• 
  
• OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST
  
• ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ----------
  
• LUOPING    htz1       USERS           HTZ1_A          2012-05-30 23:35:25        3437 VALID    DEFAULT    NORMAL        1000000 A                        1
  

　　

　　
下面对两张表进行删除5W行记录：
　　

• SQL> delete from htz1 where rownum  delete from htz2 where rownum  commit;
  

　　

　　查看表站用BLOCK的情况：
　　

• SQL> exec show_space('HTZ1','LUOPING');
  
• Unformatted Blocks .....................              62
  
• FS1 Blocks (0-25) ......................               0
  
• FS2 Blocks (25-50) .....................               0
  
• FS3 Blocks (50-75) .....................               1
  
• FS4 Blocks (75-100).....................             128
  
• Full Blocks        .....................           1,439
  
• Total Blocks............................           1,664
  
• Total Bytes.............................      13,631,488
  
• Total MBytes............................              13
  
• Unused Blocks...........................               0
  
• Unused Bytes............................               0
  
• Last Used Ext FileId....................               4
  
• Last Used Ext BlockId...................           7,561
  
• Last Used Block.........................             128
  
• 
  
• PL/SQL procedure successfully completed.
  
• 
  
• SQL> exec show_space('HTZ2','LUOPING');
  
• Unformatted Blocks .....................              62
  
• FS1 Blocks (0-25) ......................               0
  
• FS2 Blocks (25-50) .....................               0
  
• FS3 Blocks (50-75) .....................               1
  
• FS4 Blocks (75-100).....................             128
  
• Full Blocks        .....................           1,439
  
• Total Blocks............................           1,664
  
• Total Bytes.............................      13,631,488
  
• Total MBytes............................              13
  
• Unused Blocks...........................               0
  
• Unused Bytes............................               0
  
• Last Used Ext FileId....................               4
  
• Last Used Ext BlockId...................           7,433
  
• Last Used Block.........................             128
  
• 
  
• PL/SQL procedure successfully completed.
  

　　

　　下面对表进行MOVE与SHRINK
　　

• SQL> alter table luoping.htz2 enable row movement;
  
• 
  
• Table altered.
  
• 
  
• SQL> alter table luoping.htz2  shrink space;
  
• SQL> alter table luoping.htz1 move;
  
• 
  
• Table altered.
  

　　

　　下面查看表LOCK消息：
　　在SHRINK的时候，产生了行级row-X (SX)，在MOVE的时候产生了表级exclusive (X)，如果是在生产环境一定得注意这个。
　　

• [oracle@oracle10grac1 dbmonitor]$ dblock orcl1
  
• ACC1 SESSION LOCK STATUS!
  
• 
  
•     XIDUSN    XIDSLOT     XIDSQN SESSION_ID ORACLE_USE OBJECT_NAME          LOCKED_MODE   OBJECT_TYPE
  
• ---------- ---------- ---------- ---------- ---------- -------------------- ------------- --------------------
  
•          7          1       1079        151 SYS        HTZ2                 row-X (SX)    TABLE
  
• 
  
• ACC1 SESSION LOCK STATUS!
  
• 
  
•     XIDUSN    XIDSLOT     XIDSQN SESSION_ID ORACLE_USE OBJECT_NAME          LOCKED_MODE   OBJECT_TYPE
  
• ---------- ---------- ---------- ---------- ---------- -------------------- ------------- --------------------
  
•          5         39       1321        151 SYS        HTZ2                 row-X (SX)    TABLE
  
• 
  
• ACC1 SESSION LOCK STATUS!
  
• 
  
• no rows selected
  
• 
  
• ACC1 SESSION LOCK STATUS!
  
• 
  
• no rows selected
  
• 
  
• ACC1 SESSION LOCK STATUS!
  
• 
  
•     XIDUSN    XIDSLOT     XIDSQN SESSION_ID ORACLE_USE OBJECT_NAME          LOCKED_MODE   OBJECT_TYPE
  
• ---------- ---------- ---------- ---------- ---------- -------------------- ------------- --------------------
  
•          6         19       1303        151 SYS        HTZ1                 exclusive (X) TABLE
  

　　

　　下面查看表与索引的消息：
　　MOVE的时候表上的索引会自动的不可能用，因为表中的数据是基于ROWID，而在做MOVE的时候ROWID发生了变化。而SHRINK表的时候，会自己更新索引的消息。
　　

• [oracle@oracle10grac1 dbmonitor]$ dbtable orcl1 htz1
  
• 
  
• OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE)
  
• ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ----------------
  
• LUOPING    HTZ1       USERS      2012-05-30 23:54:42      1478     N DEFAULT             1 NO  N    12M                10                953347               10
  
• 
  
• OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST
  
• ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ----------
  
• LUOPING    htz1       USERS           HTZ1_A          2012-05-30 23:48:22        3328 UNUSABLE DEFAULT    NORMAL         950001 A                        1
  
• [oracle@oracle10grac1 dbmonitor]$ dbtable orcl1 htz2
  
• 
  
• OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE)
  
• ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ----------------
  
• LUOPING    HTZ2       USERS      2012-05-30 23:48:00      1630     N DEFAULT             1 NO  N    11.5625M           10                945834               10
  
• 
  
• OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST
  
• ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ----------
  
• LUOPING    htz2       USERS           HTZ2_A          2012-05-30 23:48:10        3328 VALID    DEFAULT    NORMAL         950001 A
  

　　

　　手动对相关索引rebuild online一次
　　下面查看表占用块的消息：
　　

• SQL> exec show_space('HTZ1','LUOPING');
  
• Unformatted Blocks .....................               0
  
• FS1 Blocks (0-25) ......................               0
  
• FS2 Blocks (25-50) .....................               0
  
• FS3 Blocks (50-75) .....................               0
  
• FS4 Blocks (75-100).....................               0
  
• Full Blocks        .....................           1,446
  
• Total Blocks............................           1,536
  
• Total Bytes.............................      12,582,912
  
• Total MBytes............................              12
  
• Unused Blocks...........................              58
  
• Unused Bytes............................         475,136
  
• Last Used Ext FileId....................               4
  
• Last Used Ext BlockId...................           9,097
  
• Last Used Block.........................              70
  
• 
  
• PL/SQL procedure successfully completed.
  
• 
  
• SQL> exec show_space('HTZ2','LUOPING');
  
• Unformatted Blocks .....................               0
  
• FS1 Blocks (0-25) ......................               0
  
• FS2 Blocks (25-50) .....................               1
  
• FS3 Blocks (50-75) .....................               0
  
• FS4 Blocks (75-100).....................               0
  
• Full Blocks        .....................           1,441
  
• Total Blocks............................           1,480
  
• Total Bytes.............................      12,124,160
  
• Total MBytes............................              11
  
• Unused Blocks...........................               6
  
• Unused Bytes............................          49,152
  
• Last Used Ext FileId....................               4
  
• Last Used Ext BlockId...................           6,921
  
• Last Used Block.........................              66
  

　　

　　相关的值已经明显下降。