Transforming a heap table to a partitioned table - how and whats new in 12c R1 & R2

As part of the daily operational job, one of the typical requests we DBAs get is to convert a regular (heap) table into a partitioned table. This can be achieved either offline or online. This blog will demonstrate some of the pre-12c methods and enhancements in Oracle 12c R1 and R2.

I remembered when I  had such requests in the past, I used the following offline/online methods to achieve the goals, whatever best fit my application needs.

The offline method involves the following action sequence:

1. Create empty interim partitioned table, indexes and etc
2. Stop the application services if the non-partitioned table involved in any operations
3. Migrate the data from the non-partitioned table to partitioned table
4. Swap the table names
5. Drop the non-partitioned table
6. Compile any invalid package/procedure/functions/triggers
7. Gather table stats

Note: If any integrity references, dependencies exists, the above procedure slightly defers with a couple of additional actions. The downside of this workaround is the service interruption during the course of transformation.

To avoid any service interruption, Oracle provides redefinition feature to perform the action online, without actually impacting the going DML operations on the table. The redefinition option involves the following action sequence:

1.  Validate if the table can use redefinition feature or not (DBMS_REDEFINITION.CAN_REDEF_TABLE procedure)
2.  Create interim partition table and all indexes
3. Start the online redefinition process (DBMS_REDEFINITION.START_REDEF_TABLE procedure)
4. Copy dependent objects (DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS procedure)
5. Perform data synchronization (DBMS_REDEFINITION.SYNC_INTERIM_TABLE procedure)
6. Stop the online redefinition process (DBMS_REDEFINITIONS.FINISH_REDEF_TABLE procedure)
7. Swap the table names
8. Compile any invalid package/procedure/functions/triggers
9. Gather table stats

 However, such sort of action is simplified in Oracle 12c R1 and made easier in R2. The following demonstraes12c R1 and R2 methods.

12cR1 EXCHANGE PARTITION
With EXCHANGE PARTITION feature, the data can be quickly loaded from a non-partitioned table to a partitioned table:

Once you have the partitioned table, use the following example to exchange the data of heap table to partitioned table. In this example, the existing data will be copied to a single partition in the partitioned table.

ALTER TABLE sales EXCHANGE PARTITION p1 WITH TABLE non_sales_part;

12cR2 MODIFY 
With 12cR2 ALTER TABLE MODIFY option, a non-partitioned table can be easily transformed into a partitioned table, either offline or online. The example below demonstrate creating daily interval partition:

offline procedure:
ALTER TABLE sales MODIFY
PARTITION BY RANGE (column_name) INTERVAL (1)
(partition p1 values less than (100),
partitionp2 values less than (1000)) ;

Online procedure:
ALTER TABLE sales MODIFY
PARTITION BY RANGE (column_name) INTERVAL (1)
(partition p1 values less than (100),
partitionp2 values less than (1000))ONLINE UPDATE INDEXES (index1, index2 LOCAL) ;


References:
https://uhesse.com/2010/02/15/partitioning-a-table-online-with-dbms_redefinition/
https://docs.oracle.com/database/122/VLDBG/evolve-nopartition-table.htm#VLDBG-GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484
https://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition