以前版本中move table不能够online, move 会引rowid改变使对应的索引失效。 12c 中 alter table move online不会对新事务阻塞同时会自动的维护索引的有效性。-- 创建实验表SQL> create table andy_move (id int,name varchar2(10));Table created.-- 插入数据SQL> beginfor i in 1 .. 39 loopinsert into andy_move values(i,'andyi');end loop ;commit;end;/PL/SQL procedure successfully completed.-- 创建索引SQL> create index idx_andy_id on andy_move(id);Index created.-- 查看索引状态SQL>col index_name for a25 select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from user_Indexes where index_name ='IDX_ANDY_ID';TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP------------------------- ------------------------- -------- ---------- ----------- ---ANDY_MOVE IDX_ANDY_ID VALID 0 0 NO-- move online 非分区表,带 online 参数SQL> alter table andy_move move online;Table altered.-- 查看索引状态SQL>col index_name for a25 select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from user_Indexes where index_name ='IDX_ANDY_ID';TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP------------------------- ------------------------- -------- ---------- ----------- ---ANDY_MOVE IDX_ANDY_ID VALID 0 0 NO-- move online 分区表 报错SQL> alter table p_andy move online;alter table p_andy move online*ERROR at line 1:ORA-14808: table does not support ONLINE MOVE TABLE-- move 非分区表 ,不带 online 参数delete from andy_move where id>10 and id<20;SQL> alter table andy_move move;Table altered.-- 查看索引状态SQL>col index_name for a25 select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from user_Indexes where index_name ='IDX_ANDY_ID';TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP------------------------- ------------------------- -------- ---------- ----------- ---ANDY_MOVE IDX_ANDY_ID UNUSABLE 0 1 NO
posted on 2017-05-14 11:53 张冲andy 阅读(...) 评论(...) 编辑 收藏
来源: http://www.cnblogs.com/andy6/p/6852061.html