Oracle 12C 新特性之表分区或子分区的在线迁移
Oracle 12c 中迁移表分区或子分区到不同的表空间不再需要复杂的过程。与之前版本中未分区表进行在线迁移类似，表分区或子分区可以在线或是离线迁移至一个不同的表空间。当指定了 ONLINE 语句，所有的 DML 操作可以在没有任何中断的情况下，在参与这一过程的分区或子分区上执行。与此相反，分区或子分区迁移如果是在离线情况下进行的，DML 操作是不被允许的。
-- 创建实验表
CREATE TABLE p_andy
(ID number(10), NAME varchar2(40))
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30),
PARTITION p4 VALUES LESS THAN (40)
);
Table created.
-- 插入数据
SQL>
begin
for i in 1 .. 39 loop
insert into p_andy values(i,'andyi');
end loop ;
commit;
end;
/
PL/SQL procedure successfully completed.
-- 创建一个全局非分区索引
SQL> create index idx_pandy_id on p_andy(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_PANDY_ID';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP
------------------------- ------------------------- -------- ---------- ----------- ---
P_ANDY IDX_PANDY_ID VALID 0 1 NO
-- 查看表分区状态与分区所在的表空间
SQL> select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME HIGH_VALUE
------------------------- ------------------------- ------------------ ------------------------------ ------------
P_ANDY P1 1 USERS 10
P_ANDY P2 2 USERS 20
P_ANDY P3 3 USERS 30
P_ANDY P4 4 USERS 40
-- 迁移表分区p1表空间 ，并带 UPDATE INDEXES ONLINE 参数。
SQL> ALTER TABLE p_andy move PARTITION p1 TABLESPACE bbb UPDATE INDEXES ONLINE;
Table altered.
说明:参数 UPDATE INDEXES ONLINE 迁移表分区或子分区时维护表上任何本地或全局的索引。此外，当使用ONLINE 语句时，DML 操作是不会中断的。引入加锁机制来完成这一过程，当然它也会导致性能下降并会产生大量的 redo，这取决于分区和子分区的大小。
-- 查看表分区状态与分区所在的表空间
SQL> select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME HIGH_VALUE
------------------------- ------------------------- ------------------ ------------------------------
P_ANDY P1 1 BBB 10
P_ANDY P2 2 USERS 20
P_ANDY P3 3 USERS 30
P_ANDY P_MERGE 4 USERS 40
-- 查看索引状态
SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP
------------------------- ------------------------- -------- ---------- ----------- ---
P_ANDY IDX_PANDY_ID VALID 0 1 YES
-- 迁移表分区p2表空间 ，不带 UPDATE INDEXES ONLINE 参数。
SQL> ALTER TABLE p_andy move PARTITION p2 TABLESPACE bbb ;
Table altered.
-- 查看索引状态
SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP
------------------------- ------------------------- -------- ---------- ----------- ---
P_ANDY IDX_PANDY_ID UNUSABLE 0 1 NO
说明：不带 UPDATE INDEXES ONLINE 参数，索引会失效，需要手工 rebulid 。
-- 重建索引
SQL> ALTER INDEX IDX_PANDY_ID REBUILD PARALLEL (DEGREE 2);
Index altered.
-- 查看索引状态
SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP
------------------------- ------------------------- -------- ---------- ----------- ---
P_ANDY IDX_PANDY_ID VALID 0 1 NO
补充：如果分区或分区索引比较大，可以使用并行move或rebuild，PARALLEL (DEGREE 2) 如：
ALTER TABLE table_name move SUBPARTITION sub_name TABLESPACE tts_name PARALLEL (DEGREE 2);
ALTER INDEX idx_name REBUILD TABLESPACE tts_name PARALLEL (DEGREE 2);
ALTER INDEX local_name REBUILD SUBPARTITION sub_name TABLESPACE tts_name PARALLEL (DEGREE 2);
