This is one of the new feature of oracle 12.2 release .

Non-partitioned tables can be converted to partitioned table online without any downtime to the application , i.e no impact to the DML activities.

Till now for this activity, we were using dbms_redef methods. But in Oracle 12.2 release this has been simplified much.

See the below example:

1. Identify the non partitioned table.

2. Alter table modify to partition the table.( partition key is column CREATED )

This activity will take some time depending upon amount of data table has.

While this alter statement is running, I have started running DML activities on the same table, To check whether it is impacting the DML activities.


Lets check for blocking session:

We can see that the insert statement(SID 7), is blocking the alter table command(SID 490), not the other way around. It means during this partition conversion activity, if any DML requests are coming, then it will allow them to complete their request. This may slow down the partition conversion time, But it won’t impact the application. Once ALTER TABLE MODIFY is completed. Check the whether table was partitioned properly or not.

But what happened to the INDEXES:

We can see ORDER_TAB_IND1 was NON partitioned, But ORDER_TAB_IND2 was partitioned.

Oracle document Says:

If no index clause is mentioned in the alter table statement, then
nonprefixed indexes(i.e index column is not a partitioned key) will be become global non-partitioned Index.
prefixed indexes(i.e index column is a partitioned key) will become local partitioned Index.

There are lot of 12.2 New features like moving a table online, Spliting a partitioned table online etc. which we will discuss very soon.

Leave a Reply