alter table move和 shrink space

参考文章：
http://www.2cto.com/database/201201/117275.html
http://blog.csdn.net/huangchao_sky/article/details/8700394
http://www.2cto.com/database/201201/117816.html
http://www.cnblogs.com/Cratical/archive/2010/08/24/1806948.html

切记：
move操作会锁表。
如果是很小的表，可以在线做。
如果是大表一定要注意，会长时间锁表，只能查询，影响正常业务运行。
move操作会使索引失效，一定要rebuild。
（在这方面吃过亏，所以记得很清楚。）

shrink操作期间可以对表进行增删改查。

简单总结对比

alter table table_name move和alter table table_name shrink space都可以用来收缩段，消除部分行迁移（Row Migration） 消除空间碎片，使数据更紧密，降低高水位HWM。
但是有如下区别：

1. 使用alter table move，会把表格最多收缩到创建表格时的storage子句指定的初始大小，使用alter table shrink space，则不受此限制。
   （也就是说，如果创建表的DDL语句指明了初始大小为10M，数据量最大时占用15M，假定现在表的数据只占用了2M，那么move操作只能将表占用空间减小到10M，HWM为2M。而shrink可以将表的空间占用和HWM都降到2M。）
   （如果想是用move的方式收缩到初始化大小以下，需要在alter move语句中加初始化参数。）
2. 使用alter table move之后，索引会无效，需要重建，因为move操作会改变一些记录的ROWID，所以MOVE之后索引会变为无效，需要REBUILD。使用alter table shrink space，则不会使索引无效，因为使用shrink space时，索引会自动维护。
3. 只能在表格所在的表空间是自动段空间管理（创建tablespace时指定了SEGMENT SPACE MANAGEMENT AUTO子句）的时候，才能使用alter table shrink space。
4. 如果在业务繁忙时做压缩，可以使用alter table shrink space compact来对表格进行碎片整理，而不调整HWM，之后再次调用alter table shrink space来释放空间。
5. 可以使用alter table shrink space cascade来同时对索引都进行收缩，这等同于同时执行alter index shrink space。
6. alter table t move和alter table t shrink space都可以用来消除行链接（Row Chaining）和行迁移（Row Migration）。
   为此需要先建立chained_rows表格。
   首先执行$ORACLE_HOME/RDBMS/ADMIN/utlchain.sql脚本建立chained_rows表格，
   然后执行analyze table xxx list chained rows [into chained_rows]，如果存在行链接或者行迁移，查询chained_rows就能找到发生了行链接或者行迁移的行。
   然后执行alter table move或shrink space，然后再次查看chained_rows表即可验证行链接是否消失。

下面做实验验证一下：

第一个区别

drop table TEST_MOVE;CREATE TABLE TEST_MOVE   (IP VARCHAR2(30 BYTE),     CHECKDATE VARCHAR2(30 BYTE),     CMDTYPE VARCHAR2(50 BYTE),     CONFIGDETAIL VARCHAR2(500 BYTE)   ) SEGMENT CREATION IMMEDIATE   STORAGE(INITIAL 10485760 NEXT 1048576)  TABLESPACE USERS;SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST_MOVE';SEGMENT_NAME                      EXTENTS     BLOCKS       INIT------------------------------ ---------- ---------- ----------TEST_MOVE                               3       1280         10--TEST表初始分配了10M的空间，可以看到有10个EXTENTS，1280个BLOCKS。USER_TABLES视图显示有0个使用的BLOCKS，1280个空闲BLOCKS，即该10M空间内的BLOCK都还没被ORACLE”格式化”。SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST_MOVE';TABLE_NAME                         BLOCKS EMPTY_BLOCKS------------------------------ ---------- ------------TEST_MOVE--向表中插入数据SQL> insert into TEST_MOVE select * from information;122513 rows created.SQL> analyze table TEST_MOVE compute statistics;Table analyzed.SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST_MOVE';SEGMENT_NAME                      EXTENTS     BLOCKS       INIT------------------------------ ---------- ---------- ----------TEST_MOVE                               3       1280         10SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST_MOVE';TABLE_NAME                         BLOCKS EMPTY_BLOCKS------------------------------ ---------- ------------TEST_MOVE                            1006          274--插入数据后，分配的空间仍不变，因为10个EXTENTS还没使用完。显示使用了1006个BLOCKS，空闲274个BLOCKS。这时候的1006 BLOCKS即是高水位线。SQL> commit;Commit complete.SQL> select count(*) from test_move;  COUNT(*)----------    122513SQL> delete from test_move  where rownum<=50000;50000 rows deleted.SQL> analyze table test_move compute statistics;Table analyzed.SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST_MOVE';SEGMENT_NAME                      EXTENTS     BLOCKS       INIT------------------------------ ---------- ---------- ----------TEST_MOVE                               3       1280         10SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST_MOVE';TABLE_NAME                         BLOCKS EMPTY_BLOCKS------------------------------ ---------- ------------TEST_MOVE                            1006          274SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test_move;USED_BLOCKS-----------        573--这边可以看到，删掉部分数据后，仍然显示使用了1006个BLOCKS，高水位没变。但查询真正使用的BLOCK数只有573个。所以DELETE操作是不会改变HWM的。SQL> alter table TEST_MOVE move;   Table altered.SQL> analyze table TEST_MOVE compute statistics;Table analyzed.SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST_MOVE';SEGMENT_NAME                      EXTENTS     BLOCKS       INIT------------------------------ ---------- ---------- ----------TEST_MOVE                               3       1280         10SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST_MOVE';TABLE_NAME                         BLOCKS EMPTY_BLOCKS------------------------------ ---------- ------------TEST_MOVE                             592          688--MOVE之后,HWM降低了，空闲块也上去了。--但是分配的空间并没有改变，仍然是1280个BLOCKS。--下面来看使用shrink space的方式会产生什么样的结果。SQL> SQL> alter table TEST_MOVE enable row movement;Table altered.SQL> alter table TEST_MOVE  shrink space;Table altered.SQL> analyze table TEST_MOVE compute statistics;Table analyzed.SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST_MOVE';SEGMENT_NAME                      EXTENTS     BLOCKS       INIT------------------------------ ---------- ---------- ----------TEST_MOVE                               1        600         10SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST_MOVE';TABLE_NAME                         BLOCKS EMPTY_BLOCKS------------------------------ ---------- ------------TEST_MOVE                             592            8--所以MOVE并不算真正意义上的压缩空间，只会压缩HWM以下的空间，消除碎片。我们一般建表时没有指定initial参数(默认是8个BLOCK)，也就感觉不到这个差异。而SHRINK SPACE真正做到了对段的压缩，包括初始分配的也压了，所以它是below and above HWM操作。--至于需要哪种方法，得看你的需求来了，需要分析表的增长情况，要是以后还会达到以前的HWM高度，那显然MOVE是更合适的，因为SHRINK SPACE还需要重新申请之前放掉的空间，无疑增加了操作。

第二个区别

SQL> drop table test_move;Table dropped.SQL>  create table TEST_MOVE (id int, name char(2000)) tablespace users;  Table created.SQL> insert into TEST_MOVE values (1,'aa'); SQL> insert into TEST_MOVE values (2,'bb');  SQL> insert into TEST_MOVE values (3,'cc');  1 row created.SQL> 1 row created.SQL> 1 row created.SQL> insert into TEST_MOVE values (4,'dd');  1 row created.SQL> insert into TEST_MOVE values (5,'ee');  1 row created.SQL> insert into TEST_MOVE values (6,'ff');  1 row created.SQL> insert into TEST_MOVE values (7,'gg');  1 row created.SQL> insert into TEST_MOVE values (8,'hh');  1 row created.SQL> commit;Commit complete.SQL> SQL> select rowid from TEST_MOVE;ROWID------------------AAAShiAAEAAAAbVAAAAAAShiAAEAAAAbVAABAAAShiAAEAAAAbVAACAAAShiAAEAAAAbWAAAAAAShiAAEAAAAbWAABAAAShiAAEAAAAbWAACAAAShiAAEAAAAbXAAAAAAShiAAEAAAAbXAAB8 rows selected.SQL> select Dbms_Rowid.rowid_block_number(rowid)  from TEST_MOVE;DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------                                1749                                1749                                1749                                1750                                1750                                1750                                1751                                17518 rows selected.SQL> delete from TEST_MOVE where mod(id,2)=1;   4 rows deleted.SQL> select Dbms_Rowid.rowid_block_number(rowid)  from TEST_MOVE;DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------                                1749                                1750                                1750                                1751SQL> alter table TEST_MOVE move;   Table altered.SQL> select Dbms_Rowid.rowid_block_number(rowid)  from TEST_MOVE;DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------                                1755                                1755                                1755                                1756SQL> select id,Dbms_Rowid.rowid_block_number(rowid)  from TEST_MOVE;        ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)---------- ------------------------------------         2                                 1755         4                                 1755         6                                 1755         8                                 1756--move操作后，数据的rowid发生了改变，我们知道，index是通过rowid来fetch数据行的，所以，table上的index是必须要rebuild的。--查看索引状态是否失效SQL> create index test_move_index on TEST_MOVE(id);Index created.SQL>  select index_name,status from user_indexes where index_name='test_move_index';no rows selectedSQL> select index_name,status from user_indexes where index_name='test_move_index';no rows selectedSQL> select index_name,status from user_indexes where index_name='TEST_MOVE_INDEX';INDEX_NAME                     STATUS------------------------------ --------TEST_MOVE_INDEX                VALIDSQL> alter table TEST_MOVE move;Table altered.SQL> select index_name,status from user_indexes where index_name='TEST_MOVE_INDEX';INDEX_NAME                     STATUS------------------------------ --------TEST_MOVE_INDEX                UNUSABLE--从这里可以看到，当TEST_MOVE进行move操作后，该table 上的inedx的状态为UNUSABLE，这时，我们可以使用alter index TEST_MOVE_INDEX rebuild online的命令，进行在线rebuild。  SQL> alter index TEST_MOVE_INDEX rebuild online;Index altered.SQL> select index_name,status from user_indexes where index_name='TEST_MOVE_INDEX';INDEX_NAME                     STATUS------------------------------ --------TEST_MOVE_INDEX                VALID--另外当我们对表进行move 的时候，需要加锁，我们可以看下  建立一个大表:  SQL> create table test_move as select a.* from  information a,(select * from information b where rownum <50) b;Table created.SQL> alter table test_move move;Table altered.--打开另外一个会话，可以看到:  SQL> SQL> SELECT b.session_id AS sid,    2           NVL(b.oracle_username, '(oracle)') AS username,    3           a.owner AS object_owner,    4           a.object_name,    5           Decode(b.locked_mode, 0, 'None',    6                                 1, 'Null (NULL)',    7                                 2, 'Row-S (SS)',    8                                 3, 'Row-X (SX)',    9                                 4, 'Share (S)',   10                                 5, 'S/Row-X (SSX)',   11                                 6, 'Exclusive (X)',   12                                 b.locked_mode) locked_mode,   13           b.os_user_name   14    FROM   dba_objects a,   15           v$locked_object b   16    WHERE  a.object_id = b.object_id;       SID USERNAME                       OBJECT_OWNER---------- ------------------------------ ------------------------------OBJECT_NAME--------------------------------------------------------------------------------LOCKED_MODE                              OS_USER_NAME---------------------------------------- ------------------------------        33 LIJINGKUAN                     LIJINGKUANTEST_MOVEExclusive (X)                            oracleSQL> SQL> --这里是6号锁，独占锁。  --这就意味着，table在进行move操作时，我们只能对它进行select的操作。反过来说，当我们的一个session对table进行DML操作且没有commit时，在另一个session中是不能对这个table进行move操作的，否则oracle会返回这样的错误信息：ORA-00054 。--我们还可以使用别的方法来降低table的HWM,比如shrink,CTAS 等操作,其实到现在可以看出move操作也可以用来解决table中的行迁移的问题。 

move普通表，普通索引

alter table tab_name move tablespace tbs_name;alter index index_name rebuild;alter index index_name rebuild tablespace tbs_name;

move过的普通表，在不用到失效的索引的操作语句中，语句执行正常，但如果操作的语句用到了索引（主键当做唯一索引），则此时报告用到的索引失效，语句执行失败，其他如外键，非空约束，缺省值等不会失效。
提示：查询表所具有的索引，可以使用user_indexes视图（索引和主键都在这个视图里可找到）。

move分区表及索引

move分区表及索引和普通表一样，索引会失效，区别的仅仅是语法而已。
分区基本语法：特别提醒注意，如果是单级分区，则使用关键字PARTITION，如果是多级分区，则使用SUBPARTITION替代PARTITION。
如果分区或分区索引比较大，可以使用并行move或rebuild，PARALLEL (DEGREE 2);如：

ALTER TABLE PART_ALARMTEXTDATA move SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);ALTER INDEX GLOBAL_ALARMTEXTDATA REBUILD tablespace users PARALLEL (DEGREE 2);ALTER INDEX LOCAL_ALARMTEXTDATA REBUILD SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);

移动表的某个分区：ALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name;重建全局索引：ALTER INDEX global_index REBUILD;或ALTER INDEX global_index REBUILD tablespace tbs_name;注: 分区操作时可以带上with update global indexes选项更新全局索引重建局部索引：ALTER TABLE tab_name MODIFY PARTITION partition_name REBUILD UNUSABLE LOCAL INDEXES;或ALTER INDEX local_index_name REBUILD PARTITION partition_name TABLESPACE tbs_name;

move LONG,LOB类型

LONG类型不能通过MOVE来传输特别提示，尽量不要用LONG类型，特难管理。
LONG不能使用insert into … select …等带select的模式。
insert into t123(id,en) select * from t123;报告错误，可以用pl/sql来帮助解决，如：

declarecursor cur_t123 is select * from t123;use_t123 cur_t123%rowtype;beginopen cur_t123;loopfetch cur_t123 into use_t123;exit when cur_t123%notfound;insert into t123(id,en) values (use_t123.id,use_t123.en);end loop;close cur_t123;end;

LOB类型在建立含有lob字段的表时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据（segment_type=LOBSEGMENT），另一个用来存放索引（segment_type=LOBINDEX）。默认它们会存储在和表一起的表空间。我们对表MOVE时，LOG类型字段和该字段的索引不会跟着MOVE，必须要单独来进行MOVE，语法如下如：

alter table t321 move tablespace users;alter table t321 move lob(en) store as (tablespace users);

shrink操作期间可以对表进行增删改查，索引也不会失效。

--shrink space操作所加的锁为3级锁。SQL> SELECT b.session_id AS sid,    2           NVL(b.oracle_username, '(oracle)') AS username,           a.owner AS object_owner,    3    4           a.object_name,    5           Decode(b.locked_mode, 0, 'None',    6                                 1, 'Null (NULL)',    7                                 2, 'Row-S (SS)',    8                                 3, 'Row-X (SX)',    9                                 4, 'Share (S)',   10                                 5, 'S/Row-X (SSX)',   11                                 6, 'Exclusive (X)',   12                                 b.locked_mode) locked_mode,   13           b.os_user_name   14    FROM   dba_objects a,   15           v$locked_object b   16    WHERE  a.object_id = b.object_id;       SID USERNAME                       OBJECT_OWNER---------- ------------------------------ ------------------------------OBJECT_NAME--------------------------------------------------------------------------------LOCKED_MODE                              OS_USER_NAME---------------------------------------- ------------------------------        33 LIJINGKUAN                     LIJINGKUANTEST_MOVERow-X (SX)                               oracle--新开一个sessin，执行DML语句，不提交，查看此时的锁类型。SQL> delete from test_move where IP='10.1.80.253';SQL> /       SID USERNAME                       OBJECT_OWNER---------- ------------------------------ ------------------------------OBJECT_NAME--------------------------------------------------------------------------------LOCKED_MODE                              OS_USER_NAME---------------------------------------- ------------------------------        33 LIJINGKUAN                     LIJINGKUANTEST_MOVERow-X (SX)                               oracle         1 LIJINGKUAN                     LIJINGKUANTEST_MOVERow-X (SX)                               Administrator--DML提交之后查看锁的状态。SQL> /       SID USERNAME                       OBJECT_OWNER---------- ------------------------------ ------------------------------OBJECT_NAME--------------------------------------------------------------------------------LOCKED_MODE                              OS_USER_NAME---------------------------------------- ------------------------------        33 LIJINGKUAN                     LIJINGKUANTEST_MOVERow-X (SX)                               oracle

---

SQL> create index test_move_index on TEST_MOVE(ip);Index created.SQL> select index_name,status from user_indexes where index_name='TEST_MOVE_INDEX';INDEX_NAME                     STATUS------------------------------ --------TEST_MOVE_INDEX                VALIDSQL> select count(*) from test_move;                COUNT(*)----------   3002663SQL> delete from test_move where rownum <=1500000;1500000 rows deleted.SQL> select index_name,status from user_indexes where index_name='TEST_MOVE_INDEX';INDEX_NAME                     STATUS------------------------------ --------TEST_MOVE_INDEX                VALIDSQL> ALTER TABLE test_move shrink space;Table altered.SQL> select index_name,status from user_indexes where index_name='TEST_MOVE_INDEX';INDEX_NAME                     STATUS------------------------------ --------TEST_MOVE_INDEX                VALID