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

We can now take a look at how move partition works normally when not running it in ONLINE mode.
Let’s execute

and in another session let’s run a DML while compression of P1 is still running

The update operation is put on hold and executed only after the move partition compress finishes. If you run the following query

when both operations are running, you will see clearly that compression puts an exclusive lock on the partition so any DML has to wait:

Note that if you first execute a DML without commit/rollback and from another session try to move the partition you get

A drawback of the move partition command is that makes all indexes at partition/global level invalid. We can verify that executing

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

Now let’s try to get advantage of the online move partition running the exact same test case. In one session we can execute

and before it ends in another one session we run

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

As soon as you release the DML lock the compress operation ends. Because we used the ONLINE option our indexes are all 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.

Share this post on Share on Facebook
Tweet about this on Twitter
Share on LinkedIn
Email this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *