Wednesday, August 30, 2017

How to move a partition online from Oracle 12.1 and onwards


From Oracle version 12.1 and ownwards, moving partitions and subpartitions becomes non-blocking DDL operations. DML against the table can continue while the subobject is being moved. Oracle guarantees that global indexes are maintained during the move operation, so you no longer have to specify the "UPDATE INDEXES ONLINE"-clause.

Remember that skipping this clause will trigger Asynchronous Global Index Maintenance during the nightly maintenance window.

To move a subpartition to a new tablespace:
ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312 ONLINE
TABLESPACE MYTABSPC;

To compress the subpartition data at the same time, use
ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312_P_OPLSUM ONLINE 
TABLESPACE MYTABSPC 
ROW STORE COMPRESS ADVANCED;

To move a partition to a new tablespace using a DOP of 4 while updating any global indexes defined on the table:
ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_05 ONLINE
TABLESPACE NEWTABLESPACE
PARALLEL 4;

To compress the partition data at the same time, use
ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_06 ONLINE
TABLESPACE NEWTABLESPACE
ROW STORE COMPRESS ADVANCED
PARALLEL 4;

Remember to change the properties for the partition and the table, too:
ALTER TABLE MYUSER.MYTABLE
MODIFY DEFAULT ATTRIBUTES FOR PARTITION P201312
TABLESPACE MYTABSPC;

ALTER TABLE MYUSER.MYTABLE 
MODIFY DEFAULT ATTRIBUTES 
TABLESPACE MYTABSPC;

A simple query to find how much space the partitions are using would be:
select partition_name,tablespace_name, round(sum(bytes)/1024/1024/1024,1) "GB"
from dba_segments
where segment_name='MYTABLE'
group by partition_name,tablespace_name
order by 1;

Example output:
PARTITION_NAME TABLESPACE_NAME GB
SYS_SUBP102302 DATA2
8,9
SYS_SUBP102303 DATA2
2
SYS_SUBP102304 DATA2
0
SYS_SUBP120071 DATA2
7,9
SYS_SUBP120072 DATA
1,8
SYS_SUBP120073 DATA2
0
SYS_SUBP12401 DATA
129,5


Source: Oracle Documentation, New Features list, Version 12.1.0.1. See section "2.2.3.4 ONLINE Move Partition"

No comments:

Post a Comment