ALTER TABLE...MOVE 相关使用方法

1.  The ALTER TABLE...MOVE statement enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota.

 （ALTER TABLE...MOVE可以对非分区表重新设置数据的存放位置、可以对分区表的一个分区移动到新的段中，甚至你能够转移到不同的表空间中。）

2.  This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE...MOVE statement with the COMPRESS keyword to store the new segment using table compression.

   The following statement moves the hr.admin_emp table to a new segment, specifying new storage parameters:

（该语句能够修改非分区、分区表的存储属性，而alter table 语句却不能设置。用ALTER TABLE...MOVE 移动表到新的段中可以用compress进行表压缩。
   下面的语句：移动Hr用户下面的admin_emp到新的段中，并且设置新的存储参数。）

   ALTER TABLE hr.admin_emp MOVE
      STORAGE ( INITIAL 20K
                NEXT 40K
                MINEXTENTS 2
                MAXEXTENTS 20
                PCTINCREASE 0 );

 

3. alter table xx move tablespace xx

如果表没有数据，则索引不会失效。但是如果有数据，需要分析表，重建索引。

文档里面提到：Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.

（移动表会导致行的rowid变化。这导致该表上面的index不可用，即标记为UNUSABLE，当用DML来操作该表时用到该索引，会引发ORA-01502 error错误。因此索引必须drop或者rebuild。该表的统计信息也会失效，所以需要重新对该表进行统计分析，即analyze table *** compute statistics。）

 但是针对ORA-01502错误，好像在我测试时并不存在。

 

测试如下:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as test
 
SQL> create table test(a number);
 
Table created
 
SQL> create index t_ind on test(a);
 
Index created
 
SQL> select tablespace_name,status from user_tables where table_name = 'TEST';
 
TABLESPACE_NAME                STATUS
------------------------------ --------
USERS                          VALID
 
SQL> select tablespace_name,status from user_indexes where index_name = 'T_IND';
 
TABLESPACE_NAME                STATUS
------------------------------ --------
USERS                          VALID
 
SQL> alter table test move tablespace example;  --把表从user空间转移到example空间上
 
Table altered
 
SQL> select tablespace_name,status from user_tables where table_name = 'TEST';
 
TABLESPACE_NAME                STATUS
------------------------------ --------
EXAMPLE                        VALID
 
SQL> select tablespace_name,status from user_indexes where index_name = 'T_IND';
 
TABLESPACE_NAME                STATUS
------------------------------ --------
USERS                          VALID
 
SQL> insert into test values(123);
 
1 row inserted

SQL> commit;
 
Commit complete
 
SQL> select tablespace_name,status from user_tables where table_name = 'TEST';
 
TABLESPACE_NAME                STATUS
------------------------------ --------
EXAMPLE                        VALID
 
SQL> select tablespace_name,status from user_indexes where index_name = 'T_IND';
 
TABLESPACE_NAME                STATUS
------------------------------ --------
USERS                          VALID
 
SQL> alter table test move tablespace users;  --把表从example空间转移到user空间上
 
Table altered
 
SQL> select tablespace_name,status from user_tables where table_name = 'TEST';
 
TABLESPACE_NAME                STATUS
------------------------------ --------
USERS                          VALID
 
--索引失效！
SQL> select tablespace_name,status from user_indexes where index_name = 'T_IND';
 
TABLESPACE_NAME                STATUS
------------------------------ --------
USERS                          UNUSABLE
 
 

相关资料：

http://blog.oracle.com.cn/?uid-55467-action-viewspace-itemid-7511

http://www.itpub.net/thread-21120-1-1.html

http://www.builder.com.cn/2007/1006/537236.shtml