12c Database : Online clause for partition movement

From 12c onwards, partitions movement has been extended with "online clause", along with this global index are maintained during partition maintenance , hence manual rebuild of index is not required any more. Before proceeding with the test, here are some restriction as listed in documentation,

	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.

Lets open two sessions to show you the online partition movements along with dml operations on another session

	Session 1
	=========
	SQL> select sys_context('USERENV', 'SID') sid from dual;

	SID
	------
	7

	Session 2
	=========
	SQL> select sys_context('USERENV', 'SID') sid from dual;

	SID
	------
	72

Lets create a table with range partition in session 1,

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

	Table created.

SQL>

Check the object id for this partition

	SQL> 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                   91681

Under session 2, insert into partition without commit anything to simulate the DML operation while move partition later

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

	SID
	------
	72

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

Under Session 1, Give the alter partition with online command

	SQL> alter table test_tbl move partition p1 ONLINE;

	Now your session waits for insert statement to commit above. 

Check the locks

	SQL> set lines 1000
	SQL> 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  ;
	  2    3    4    5    6
	SESS                                                    ID1        ID2      LMODE    REQUEST TY
	------------------------------------------------ ---------- ---------- ---------- ---------- --
	Holder: 72                                           458763       1589          6          0 TX
	Waiter: 7                                            458763       1589          0          4 TX

	SQL>

As you see the First session (7) is waiting for second session (72) where the request mode of session 1(7) is 4.

	Let's open third session and do some insert operations , this operations
	will not affect nor wait and they proceed.

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

	SID
	--------------------------------------------------------------------------------
	58

	SQL> insert into test_tbl values(3,3);

	1 row created.

	SQL> commit;

	Commit complete.

	SQL>

Well my first session operation still is going i.e online move operation, i can do the DML concurrently, which does not affect at all Lets commit the first session to proceed with non blocking.

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

	SID
	------
	72

	SQL> commit;

	Commit complete.

	SQL>

Now in my session 1, the table partition movement has been completed and the object id has also been changed to evident the online movement.

	SQL> select object_name, subobject_name, data_object_id
		from user_objects
		where object_name='TEST_TBL'
		and subobject_name='P1';  2    3    4

	OBJECT_NAME          SUBOBJECT_NAME   DATA_OBJECT_ID
	-----------------    --------------   --------------
	TEST_TBL                  P1          91682

	SQL>
	
	SQL> select * from test_tbl;
	
	       ID1        ID2
	---------- ----------
	         3          3
	         1          1
	         2          2
	
	SQL>


This feature is extremely helpful when you doing partition level re-org.
-Thanks
Geek DBA