In Oracle 12C ALTER TABLE … MOVE PARTITION has been extended with new clause ONLINE.

ALTER TABLE <table name> 
MOVE PARTITION <partition name> ONLINE

ONLINE clause indicates that DML on the table partition will be allowed while moving the table partition.

Global indexes are maintained during the move partition, so a manual index rebuild is no longer required.

Some restrictions:

• ONLINE clause can’t be specified
  • for tables owned by SYS.
  • for index-organized tables.
  • for heap-organized tables that contain object types or on which bitmap join indexes or domain indexes are defined.
  • when database-level supplemental logging is enabled for the database.
• 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.

Example

To show how it works we need three separate session. In first session (SID 258) I have created simple table with dummy data.

select sys_context('USERENV', 'SID') sid
from dual;

SID 
------------------
258

create table test_tbl
(
  id1 number,
  id2 number
)
partition by range(id1)
(
  partition p1 values less than(10)
);

insert into test_tbl values(1,1);

commit;

Our partition segment has got unique data_object_id which will change when partition is moved

select object_name, subobject_name, data_object_id 
from user_objects
where object_name='TEST_TBL'
  and subobject_name='P1';

OBJECT_NAME  SUBOBJECT_NAME DATA_OBJECT_ID
------------ -------------- --------------
TEST_TBL     P1             93037

Now we execute simple insert in second session (SID 29) without COMMIT

select sys_context('USERENV', 'SID') sid
from dual;

SID 
------------------
29

insert into test_tbl values(2,2);
1 rows inserted.

If you try to run following code in first session (SID 258) the session will hang and wait till all DML operations are completed to move partition.

select sys_context('USERENV', 'SID') sid
from dual;

SID 
------------------
258

alter table test_tbl move partition p1 ONLINE;

Now you can check that second session (SID 29) is blocking first session (SID 258)

select sys_context('USERENV', 'SID') sid
from dual;

SID 
------------------
29

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
      (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request  ;

SESS              LMODE      REQUEST    TYPE
----------------- ---------- ---------- ----
Holder: 29        6          0          TX   
Waiter: 258       0          4          TX

If you open third session (SID 321) you can still execute DML operations and insert data to partition which will be moved.

select sys_context('USERENV', 'SID') sid
from dual;

SID 
------------------
321

insert into test_tbl values(3,3);
1 rows inserted.

commit;
committed.

Once you do commit in session 2 MOVE ONLINE operation in session 1 will complete which is proved by new data_object_id assigned to moved partition 93037(old) 93038(new).

select sys_context('USERENV', 'SID') sid
from dual;

SID 
------------------
29

select object_name, subobject_name, data_object_id 
from user_objects
where object_name='TEST_TBL'
  and subobject_name='P1';

OBJECT_NAME  SUBOBJECT_NAME DATA_OBJECT_ID
------------ -------------- --------------
TEST_TBL     P1             93038

select * from test_tbl;

       ID1        ID2
---------- ----------
         1          1 
         2          2 
         3          3

Have a fun 🙂

Tomasz