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