认识达内从这里开始

准备工作，创建一张表：

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