Partitioning new features 12c

* Can move table partitions from one TS to anothes TS online:

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 in one session we have an active transaction (i.e. not yet committed):

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 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.