Thursday , September 28 2023

Oracle Database Online Partition Table

In today’s article, I will be explaining how to partition a classic table online without interruption for Oracle Database.

In our work, as usual, we will get help from the “HR” user.

The package will briefly set the table we have prepared as a partition as a materialized view and transfer the data in it here.

A materialized view log will be created in the existing table and the incoming data will be kept here as dml. After that, the tables will change and the process will end.

In our scenario, I will make use of the PERSONNEL (I created this table as a copy of hr.employees with the help of CTAS) table, which is under the HR user.

The table I will open as Partiton will be PERSONAL_PT. I use ” Interval Partitioning ” partition as partition type.

What is Interval Partitioning?

Interval Partitioning is a partition type that entered our lives with Oracle Database 11G and is used in parallel with Range partitioning.

The difference is, when you create a table with Range partitioning, the data is transferred to the partitions you specify.

With Interval Partitioning, for example, the system automatically adds a new partition if it is not in the partitions you have opened as a date. It was returning an error before.

I only create my Partition Table ( HR.PERSONEL_PT ) since I have the original table PERSONEL.

If the table to be processed is large, you can use parallelism.

Now that our table is created, we can start setting it from the DBMS_REDEFINITION package.

Now let’s see if there is a problem with our table.

Since there is no problem in the table, we can start the second step with the help of DBMS_REDEFINITION.

Note: If you reverse the table names here, you may experience data loss.

Now if you look at the state of the table, you will see that there is a partition, but the job is not finished.

In large tables, SYNC operation is important before the last step, Finish_Redef, so that the process does not take long.

Let’s sync existing data with mv.

Finally, let’s finish the process.

As you can see, it is a very simple process and can be done without interruption.

If for any reason you want to take the process from the last step to the previous step.


About Buğra PARLAYAN

Burgra Parlayan is an experienced Database and Weblogic Administrator. After completing his technical / relevant training he has got involved with a serious amount of projects. He successfully managed database upgrade, database migration, database performance tuning projects for various public institutions.Currently he has been employed by one of the leading financial institutions called Turkiye Hayat & Emeklilik as responsible administrator for Oracle Database and Oracle Middleware. He has been sharing his experience and knowledge by face to face training, personal blog and various social networking accounts to support the Oracle ecosystem continuously since 2010.

Leave a Reply

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