12c Online Partitioned Table Reorganisation Part II (Move On)

In Part I, we looked at some of the issues associated with locking and unusable indexes when Moving both tables and table partitions.

The Oracle 12c Database has introduced a number of great new capabilities associated with online DDL activities. One of these really useful features is the capability to now move table partitions online while maintaining associated indexes.

If we look at the same demo as with the previous post:

SQL> create table muse (id number, status varchar2(6), name varchar2(30))
2 partition by range (id)
3 (partition p1 values less than (1000001)
4 partition p2 values less than (2000001)
5 partition p3 values less than (maxvalue));

Table created.

SQL> insert into muse select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level >= 3000000;

3000000 rows created.

SQL> commit;

Commit complete.

SQL> create index muse_id_pk on muse(id);

Index created.

SQL> alter table muse add constraint muse_id_pk primary key(id);

Table altered.

SQL> create index muse_status_i on muse(status) local;

Index created.

If in one session we have an active transaction (i.e. not yet committed):

SQL> insert into muse values (3000001, 'OPEN', 'ZIGGY STARDUST');

1 row created.

while we move a table partition in another session as we did previously:

SQL> alter table muse move partition p3 tablespace users update indexes;

alter table muse move partition p3 tablespace users update indexes

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

We get the same resource busy error.

However, if we now use the new 12c UPDATE INDEXES ONLINE clause:

SQL> alter table muse move partition p3 tablespace users update indexes online;

The session now simply hangs until the transaction in session one completes, in a similar manner to that of an index rebuild online operation.

Once we commit in session one:

SQL> commit;

Commit complete.

The Move Partition operation can obtain the necessary table partition lock and complete successfully:

SQL> alter table muse move partition p3 tablespace users update indexes online;

Table altered.

The indexes remain in a USABLE state throughout:

SQL> select index_name, partition_name, status from dba_ind_partitions
where index_name='MUSE_STATUS_I'
union
select index_name, null, status from dba_indexes where index_name='MUSE_ID_PK';

INDEX_NAME      PARTITION_NAME       STATUS
--------------- -------------------- --------
MUSE_ID_PK                           VALID
MUSE_STATUS_I   P1                   USABLE
MUSE_STATUS_I   P2                   USABLE
MUSE_STATUS_I   P3                   USABLE

If we perform these operations the other way around with the Move Partition first:

SQL> alter table muse move partition p3 tablespace users update indexes online;

While in session two we now insert a new row into the partition being moved:

SQL> insert into muse values (3000004, 'OPEN', 'ZIGGY STARDUST');

1 row created.

The insert operation is now not locked out by the Move Partition operation and can proceed. The Move Partition operation in turn requires a brief lock at the end of the process (in much the same way as a normal online index rebuild) and so will hang if there are any outstanding transactions on the partition being moved at that time. And again like an online index rebuild, this will not in turn lock out other DML transactions.

Once the commit is performed in session two:

SQL> commit;

Commit complete.

The Move Partition can complete:

SQL> alter table muse4 move partition p3 tablespace users update indexes online;

Table altered.

And importantly, all the associated indexes again remain in a USABLE state:

SQL> select index_name, partition_name, status
from dba_ind_partitions where index_name='MUSE_CODE_I'
union select index_name, null, status from dba_indexes
where index_name='MUSE_ID_PK';

INDEX_NAME      PARTITION_NAME       STATUS
--------------- -------------------- --------
MUSE_CODE_I     P1                   USABLE
MUSE_CODE_I     P2                   USABLE
MUSE_CODE_I     P3                   USABLE
MUSE_ID_PK                           VALID

Now this is only applicable to the online move of table partitions. Moving a non-partitioned table still has the same issues and restrictions as before (as discussed in Part I).

Therefore for those with the Partitioning option, serious consideration could be given to converting tables to partitioned tables, even if the table only has the one partition:

SQL> create table muse3 (id number, status varchar2(6), name varchar2(30))
2 partition by range (id)
3 (partition p1 values less than (maxvalue));

Table created.

Such a table is effectively the equivalent of a non-partitioned table, but can now take advantage of the flexibility that the new online move partition capability provides 🙂