降低高水位线的方法

准备工作，创建一张表：
CREATE TABLE TEST2
(
    ID    NUMBER(10),
    NAME  VARCHAR2(32)
);

1、移动表：
SQL> begin
  2  for i in 1..10000 loop
  3  insert into test2 values(i,'bbb');
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL>  select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2                          LXM                                   244           12

SQL> delete test2;
100000 rows deleted.

SQL> alter table test2 move;
Table altered.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';


TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2                          LXM                                     0            8

2、收缩表：
SQL>    begin
  2  for i in 1..100000 loop
  3  insert into test2 values(i,'kkk');
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2                          LXM                                   244           12

SQL> delete test2;
100000 rows deleted.

SQL> alter table test2 shrink space;
alter table test2 shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table test2 enable row movement;
Table altered.

SQL> alter table test2 shrink space;
Table altered.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2                          LXM                                     1            7

3、 truncate表
SQL> begin
  2  for i in 1..100000 loop
  3  insert into test2 values(i,'kkk');
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2                          LXM                                   244           12

SQL> truncate table test2;
Table truncated.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2                          LXM                                     0            8

4、新建临时表，然后rename
SQL> begin
  2  for i in 1..100000 loop
  3  insert into test2 values(i,'kkk');
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> delete test2;
100000 rows deleted.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2                          LXM                                   244           12

SQL> create table test3 as select * from test2;
Table created.

SQL> drop table test2;
Table dropped.

SQL> alter table test3 rename to test2;
Table altered.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2                          LXM                                     0            0

备注：
1） move不但可以重置水位线（ HWM），解决松散表带来的 IO 浪费，还可以解决表中的行迁移问题。
    move表的话需要双倍的空间，否则无法成功。move表可以通过重新安排数据文件的空间达到收缩数据文件的目的。
    move表时，会产生 exclusive lock 锁，此时只能对表进行 select 操作。
    move表之后，如果表上有索引，记得重建。
2）shrink表只对ASSM管理的表有效，相关命令有：
    ----- alter table TABLE_NAME shrink space;     整理碎片并回收空间
    ----- alter table TABLE_NAME shrink space compact;      只整理碎片，不回收空间
     ----- alter table TABLE_NAME shrink space cascate;       整理碎片回收空间，并连同表的级联对象一起整理 ( 比如索引 )
   能在线进行，不影响表上的DML操作，当然，并发的DML操作在shrink结束的时刻会出现短暂的block；
   shrink的另外一个优点是在碎片整理结束后，表上相关的index仍然enable。
3）move的操作速度远远快于shrink 操作 ，不是一般的快，不是一个数量级的，而且shrink 会产生大量的undo 和redo 操作。
4）truncate是DDL操作，相当于删表后重建。
5）还有其他的方法，如导出后再重新导入。


==================================================
空表移动无须重建索引：
SQL> begin
  2  for i in 1..10000 loop
  3  insert into test2 values(i,'bbb');
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes; 
INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS   INITIAL_EXTENT MAX_EXTENTS
------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
IND_TEST2                      NORMAL                      TEST2                          VALID             65536  2147483645

SQL> delete test2 where id=1;
1 row deleted.
SQL> alter table test2 move;
Table altered.

SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes;
INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS   INITIAL_EXTENT MAX_EXTENTS
------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
IND_TEST2                      NORMAL                      TEST2                          UNUSABLE          65536  2147483645

SQL> alter index ind_test2 rebuild;
Index altered.

SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes;
INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS   INITIAL_EXTENT MAX_EXTENTS
------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
IND_TEST2                      NORMAL                      TEST2                          VALID             65536  2147483645

SQL> delete test2;
9999 rows deleted.
SQL> alter table test2 move;
Table altered.

SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes;
INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS   INITIAL_EXTENT MAX_EXTENTS
------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
IND_TEST2                      NORMAL                      TEST2                          VALID             65536  2147483645

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

转载于:http://blog.itpub.net/28497416/viewspace-2124762/