With the release 12c Oracle introduced the “online partition movement” making the operation a non-blocking DDL command. This means that it’s possible to run any DML on a partition while the same is being “moved” using the new ONLINE option.
Let’s see how that works on our usual test table populated with 1 million rows in the first partition
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Create table drop table mytab; create table mytab(id number(10), run_date date, status varchar2(100)) partition by range(run_date) (partition P1 values less than (date '2016-01-02'), partition P2 values less than (date '2016-01-03') ); -- Populate data insert into MYTAB select rownum, date '2016-01-01', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 1000000; insert into MYTAB select rownum, date '2016-01-02', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 100; commit; -- Create one local and one global index create index MYTAB_IDX_L on MYTAB(id) local; create index MYTAB_IDX_G on MYTAB(status); |
We can now take a look at how move partition works normally when not running it in ONLINE mode.
Let’s execute
|
1 |
alter table MYTAB move partition P1 compress for OLTP; |
and in another session let’s run a DML while compression of P1 is still running
|
1 2 3 4 |
update MYTAB set id = id where run_date = date '2016-01-01' and rownum = 1; |
The update operation is put on hold and executed only after the move partition compress finishes. If you run the following query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
SELECT session_id, owner, object_name, subobject_name, object_type, decode(s.command, 1,'Create table' , 2,'Insert', 3,'Select' , 6,'Update', 7,'Delete' , 9,'Create index', 10,'Drop index' ,11,'Alter index', 12,'Drop table' ,13,'Create seq', 14,'Alter sequence' ,15,'Alter table', 16,'Drop sequ.' ,17,'Grant', 19,'Create syn.' ,20,'Drop synonym', 21,'Create view' ,22,'Drop view', 23,'Validate index' ,24,'Create procedure', 25,'Alter procedure' ,26,'Lock table', 42,'Alter session' ,44,'Commit', 45,'Rollback' ,46,'Savepoint', 47,'PL/SQL Exec' ,48,'Set Transaction', 60,'Alter trigger' ,62,'Analyze Table', 63,'Analyze index' ,71,'Create Snapshot Log', 72,'Alter Snapshot Log' ,73,'Drop Snapshot Log', 74,'Create Snapshot' ,75,'Alter Snapshot', 76,'drop Snapshot' ,85,'Truncate table', 0,'No command', '? : '||s.command) nocommand, s.blocking_session, DECODE(l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS, DECODE(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode) ) MODE_HELD FROM gv$locked_object v, dba_objects d, gv$lock l, gv$session s WHERE v.object_id = d.object_id AND (v.object_id = l.id1) AND v.session_id = s.sid AND owner = user AND object_name = 'MYTAB' ORDER BY username, session_id, object_name, subobject_name; |
when both operations are running, you will see clearly that compression puts an exclusive lock on the partition so any DML has to wait:
|
1 2 3 4 5 6 7 |
SESSION_ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE NOCOMMAND BLOCKING_SESSION STATUS MODE_HELD 303 SCOTT MYTAB P1 TABLE PARTITION Update 315 Blocking None 303 SCOTT MYTAB TABLE Update 315 Not Blocking Row-X (SX) 303 SCOTT MYTAB TABLE Update 315 Not Blocking Row-X (SX) 315 SCOTT MYTAB P1 TABLE PARTITION Create table Blocking Exclusive 315 SCOTT MYTAB TABLE Create table Not Blocking Row-X (SX) 315 SCOTT MYTAB TABLE Create table Not Blocking Row-X (SX) |
Note that if you first execute a DML without commit/rollback and from another session try to move the partition you get
|
1 |
SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired |
A drawback of the move partition command is that makes all indexes at partition/global level invalid. We can verify that executing
|
1 2 3 4 5 6 7 8 9 10 11 |
select ui.index_name, uip.partition_name, NVL(uip.status, ui.status) status from user_indexes ui, user_ind_partitions uip where ui.table_name = 'MYTAB' and ui.index_name = uip.index_name (+) order by uip.partition_name, ui.index_name; INDEX_NAME PAR STATUS --------------- ----- -------- MYTAB_IDX_L P1 UNUSABLE MYTAB_IDX_L P2 USABLE MYTAB_IDX_G UNUSABLE |
and as you can see both the global index and the one for the partition we moved are unusable and need to be rebuilt.
We can quickly fix it by running
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
alter index MYTAB_IDX_G rebuild; alter index MYTAB_IDX_L rebuild partition P1; select ui.index_name, uip.partition_name, NVL(uip.status, ui.status) status from user_indexes ui, user_ind_partitions uip where ui.table_name = 'MYTAB' and ui.index_name = uip.index_name (+) order by uip.partition_name, ui.index_name; INDEX_NAME PARTI STATUS --------------- ----- -------- MYTAB_IDX_L P1 USABLE MYTAB_IDX_L P2 USABLE MYTAB_IDX_G VALID |
Now let’s try to get advantage of the online move partition running the exact same test case. In one session we can execute
|
1 |
alter table MYTAB move partition P1 online compress for OLTP; |
and before it ends in another one session we run
|
1 2 3 4 |
update MYTAB set id = id where run_date = date '2016-01-01' and rownum = 1; |
without commit. Are you maybe still waiting for the first operation to complete? Well, you will not see it finishing unless you close the transaction you started with the update! Yes, that’s the peculiar thing with online movement: the DDL is not blocking anymore but it cannot end if there are uncommitted transactions on the same partition.
We can again take a look at the locks and see that this time the DML is holding the DDL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SESSION_ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE NOCOMMAND BLOCKING_SESSION STATUS MODE_HELD 303 SCOTT MYTAB P1 TABLE PARTITION No command Not Blocking Row-X (SX) 303 SCOTT MYTAB P1 TABLE PARTITION No command Not Blocking Row-X (SX) 303 SCOTT MYTAB P1 TABLE PARTITION No command Not Blocking Row-X (SX) 303 SCOTT MYTAB TABLE No command Not Blocking Row-X (SX) 303 SCOTT MYTAB TABLE No command Not Blocking Row-X (SX) 303 SCOTT MYTAB TABLE No command Not Blocking Row-X (SX) 315 SCOTT MYTAB P1 TABLE PARTITION Create table 303 Not Blocking Row-X (SX) 315 SCOTT MYTAB P1 TABLE PARTITION Create table 303 Not Blocking Row-X (SX) 315 SCOTT MYTAB P1 TABLE PARTITION Create table 303 Not Blocking Row-X (SX) 315 SCOTT MYTAB TABLE Create table 303 Not Blocking Row-X (SX) 315 SCOTT MYTAB TABLE Create table 303 Not Blocking Row-X (SX) 315 SCOTT MYTAB TABLE Create table 303 Not Blocking Row-X (SX) |
As soon as you release the DML lock the compress operation ends. Because we used the ONLINE option our indexes are all valid
|
1 2 3 4 5 6 7 8 9 10 11 |
select ui.index_name, uip.partition_name, NVL(uip.status, ui.status) status from user_indexes ui, user_ind_partitions uip where ui.table_name = 'MYTAB' and ui.index_name = uip.index_name (+) order by uip.partition_name, ui.index_name; INDEX_NAME PARTI STATUS --------------- ----- -------- MYTAB_IDX_L P1 USABLE MYTAB_IDX_L P2 USABLE MYTAB_IDX_G VALID |
and this is a great way deal with big tables whose indexes require a lot of time to be rebuilt!
Interestingly, if we first run the DML leaving the lock and then execute from another session an ONLINE move operation, we don’t get any exception (like it happened for the non-online execution) but the DDL waits for the DML lock to be released.
