慎用alter table move

     如果索引和表放在同一個表空間請大家慎用alter table move，它會讓index變為UNUSABLE。

操作過程：

SQL> alter table TEST_COURSE MOVE;
已更改表格.
SQL> set time on;
09:28:48 SQL> alter table TEST_flow_dt move;
已更改表格.
09:30:15 SQL> alter table TEST_transfer move;
已更改表格.
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes  where table_name IN ('TEST_FLOW_DT','TEST_COURSE','TEST_TRANSFER');
                                                                                                                                                                
INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS                                                    
------------------------------ --------------------------- ------------------------------ ----------- --------                                                  
INX_TEST_COURSE_02           NORMAL                      TEST                            TABLE       UNUSABLE                                                  
INX_TEST_COURSE_01           NORMAL                      TEST                            TABLE       UNUSABLE                                                  
PK_TEST_COURSE               NORMAL                      TEST                            TABLE       UNUSABLE                                                  
INX_TEST_COURSE_03           NORMAL                      TEST                            TABLE       UNUSABLE                                                  
INX_TEST_FLOW_DT_04         NORMAL                      TEST                            TABLE       UNUSABLE                                                  
INX_TEST_FLOW_DT_01         NORMAL                      TEST                            TABLE       UNUSABLE                                                  
INX_TEST_FLOW_DT_07         FUNCTION-BASED NORMAL       TEST                            TABLE       UNUSABLE                                                  
INX_TEST_FLOW_DT_06         FUNCTION-BASED NORMAL       TEST                            TABLE       UNUSABLE                                                  
INX_TEST_FLOW_DT_05         NORMAL                      TEST                            TABLE       UNUSABLE                                                  
IND_TEST_FLOW_DT_03         NORMAL                      TEST                            TABLE       UNUSABLE                                                  
IND_TEST_FLOW_DT_02         NORMAL                      TEST                            TABLE       UNUSABLE                                                  
PK_TEST_FLOW_DT             NORMAL                      TEST                            TABLE       UNUSABLE                                                     
IND_TRAN_ORG                   NORMAL                      TEST                            TABLE       UNUSABLE                                                  
IND_TEST_TRANSFER02          NORMAL                      TEST                            TABLE       UNUSABLE                                                  
PK_TEST_TRANSFE              NORMAL                      TEST                            TABLE       UNUSABLE                                                  
INX_TEST_TRANSFER_01         NORMAL                      TEST                            TABLE       UNUSABLE     

 出現這種情況必須重建索引                       

alter index INX_TEST_COURSE_02   rebuild;
alter index INX_TEST_COURSE_01   rebuild;
alter index PK_TEST_COURSE       rebuild;
alter index INX_TEST_COURSE_03   rebuild;
alter index INX_TEST_FLOW_DT_04  rebuild;
alter index INX_TEST_FLOW_DT_01  rebuild;
alter index INX_TEST_FLOW_DT_07  rebuild;
alter index INX_TEST_FLOW_DT_06  rebuild;
alter index INX_TEST_FLOW_DT_05  rebuild;
alter index IND_TEST_FLOW_DT_03  rebuild;
alter index IND_TEST_FLOW_DT_02  rebuild;
alter index PK_TEST_FLOW_DT      rebuild;
alter index IND_TRAN_ORG         rebuild;
alter index IND_TEST_TRANSFER02  rebuild;
alter index PK_TEST_TRANSFE      rebuild;
alter index INX_TEST_TRANSFER_01 rebuild;
          

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

转载于:http://blog.itpub.net/16381228/viewspace-753730/