以前版本中move table不能夠online， move 會引rowid改變使對應的索引失效。 12c 中 alter table move online不會對新事務阻塞同時會自動的維護索引的有效性。

-- 建立實驗表
SQL> create table andy_move (id int,name varchar2(10));
Table created.
-- 插入資料
SQL>
begin
for i in 1 .. 39 loop
insert 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

Oracle 12C 新特性之move （非分割槽表）table online

相關文章