Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 | SQL> alter session force parallel dml parallel 8; SQL> alter session force parallel query parallel 8; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE TABLE HR.PERSONEL_PT ( EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(20 BYTE), LAST_NAME VARCHAR2(25 BYTE), EMAIL VARCHAR2(25 BYTE), PHONE_NUMBER VARCHAR2(20 BYTE), HIRE_DATE DATE, JOB_ID VARCHAR2(10 BYTE), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), DEPARTMENT_ID NUMBER(4) ) PARTITION BY RANGE (HIRE_DATE) INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') ) (PARTITION part_date1 VALUES LESS THAN (TO_DATE ('01/01/2020', 'DD/MM/YYYY')) TABLESPACE USERS, PARTITION part_date2 VALUES LESS THAN (TO_DATE ('01/02/2020', 'DD/MM/YYYY')) TABLESPACE USERS, PARTITION part_date3 VALUES LESS THAN (TO_DATE ('01/03/2020', 'DD/MM/YYYY')) TABLESPACE USERS, PARTITION part_date4 VALUES LESS THAN (TO_DATE ('01/04/2020', 'DD/MM/YYYY')) TABLESPACE USERS, PARTITION part_date5 VALUES LESS THAN (TO_DATE ('01/05/2020', 'DD/MM/YYYY')) TABLESPACE USERS); |
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.
1 2 3 4 5 6 7 | BEGIN SYS.DBMS_REDEFINITION.can_redef_table ( uname => 'HR', tname => 'PERSONEL_PT', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID); END; / |
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.
1 2 3 4 5 6 | BEGIN SYS.DBMS_REDEFINITION.start_redef_table (uname => 'HR', orig_table => 'PERSONEL', int_table => 'PERSONEL_PT'); END; / |
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.
1 2 3 4 5 6 | BEGIN DBMS_REDEFINITION.sync_interim_table (uname => 'HR', orig_table => 'PERSONEL', int_table => 'PERSONEL_PT'); END; / |
Finally, let’s finish the process.
1 2 3 4 5 6 | BEGIN DBMS_REDEFINITION.finish_redef_table (uname => 'HR', orig_table => 'PERSONEL', int_table => 'PERSONEL_PT'); END; / |
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.
1 | EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE('HR', 'PERSONEL_PT', 'PERSONEL') |
