With 12cR1 you can now move table partitions and sub-partitions online. There are lot of reasons for which you might want to move a table partition or sub-partition such as:-

• Re-cluster data and reduce fragmentation.
• Move a partition to another tablespace.
• Modify create-time attributes.
• Store the data in compressed format using table compression.

The problem with earlier versions was that when you move a partition there were no DML operations allowed during the move and also the indexes on that table can be marked as unusable depending on the table type and may require an index rebuild thus adding to your overall downtime.

In 12cR1 you can not only move a partition/sub-partition online but also the indexes remain usable so no need to rebuild the indexes.

Now we’ll see a quick demonstration of this feature.

Step-1: Create a Partitioned Table:-

CREATE TABLE t1 (id number, name varchar2(128), sub_name varchar2(128), type varchar2(30) )
PARTITION BY RANGE (id) interval (1000)
(PARTITION t1_1 VALUES LESS THAN (1000),
PARTITION t1_2 VALUES LESS THAN (2000),
PARTITION t1_3 VALUES LESS THAN (3000),
PARTITION t1_4 VALUES LESS THAN (4000),
PARTITION t1_5 VALUES LESS THAN (5000),
PARTITION t1_6 VALUES LESS THAN (6000),
PARTITION t1_7 VALUES LESS THAN (7000),
PARTITION t1_8 VALUES LESS THAN (8000),
PARTITION t1_9 VALUES LESS THAN (9000),
PARTITION t1_10 VALUES LESS THAN (10000)
);

Step-2: Insert Records:-

insert into t1 select object_id,object_name, subobject_name, object_type from dba_objects WHERE rownum<9000;
commit;

Step-3: Create Indexes:-

create index t1_id_pk on t1(id);
create index t1_type on t1(type) local;

Step-4: Move Table Partition:-

ALTER TABLE t1 MOVE PARTITION T1_5 ONLINE TABLESPACE USERS UPDATE INDEXES;

Step-5: Check Index status:-

select index_name, partition_name, status
from dba_ind_partitions where index_name='T1_TYPE'
union select index_name, null, status from dba_indexes
where index_name='T1_ID_PK';  2    3    4

INDEX_NAME              PARTITION_NAME       STATUS
-------------        -------------------- --------
T1_ID_PK                                  VALID
T1_TYPE              T1_1                 USABLE
T1_TYPE              T1_10                USABLE
T1_TYPE              T1_2                 USABLE
T1_TYPE              T1_3                 USABLE
T1_TYPE              T1_4                 USABLE
T1_TYPE              T1_5                 USABLE
T1_TYPE              T1_6                 USABLE
T1_TYPE              T1_7                 USABLE
T1_TYPE              T1_8                 USABLE
T1_TYPE              T1_9                 USABLE

11 rows selected.

That is it, you not only moved your partition ONLINE the indexes were also not made unusable, this would save lot of time for the DBAs who spent time in managing the partitions.

Restrictions of moving Partitions/Sub-Partitions Online:-

• You cannot specify the ONLINE clause for tables owned by SYS.
• You cannot specify the ONLINE clause for index-organized tables.
• You cannot specify the ONLINE clause for heap-organized tables that contain object types or on which bitmap join indexes or domain indexes are defined.
• Parallel DML and direct path INSERT operations require an exclusive lock on the table. Therefore, these operations are not supported concurrently with an ongoing online partition MOVE, due to conflicting locks.

Hope this helps, in case of any concerns or doubts or incase any assistance is required please feel free to comment below and we’ll try to assist you.