Partitioning a table online using DBMS_REDEFINITION

It is possible to make changes to the structure of tables online with the DBMS_REDEFINITION package in the Oracle database. We can convert a non-partitioned table to a partitioned structure online with DBMS_REDEFINITION. The process steps will be as follows.

In the example below, the steps required to convert the non-partitioned DEMO1 table to the partitioned DEMO table are specified.

The source table that will be processed with DBMS_REDEFINITION must have a primary key.

Let’s create a table that is not partitioned but that we want to have, with a primary key.

CREATE TABLE demo1
(
   id NUMBER (8),
   t_task DATE,
   task VARCHAR(255),
   CONSTRAINT redef_tab_pk PRIMARY KEY (id)
)
Let's create the table in the targeted structure.

CREATE TABLE demo
(
   id NUMBER (8),
   t_task DATE,
   task VARCHAR(255),
   partition_year AS (EXTRACT (YEAR FROM t_task)),
   partititon_month AS (EXTRACT (MONTH FROM t_gorev))
)
PARTITION BY RANGE (partition_year)
   SUBPARTITION BY RANGE (partititon_month)
   (PARTITION year_2011 VALUES LESS THAN (2011),
    PARTITION year_2012
       VALUES LESS THAN (2012)
       (
          SUBPARTITION p1 VALUES LESS THAN (2),
          SUBPARTITION P2 VALUES LESS THAN (3),
          SUBPARTITION P3 VALUES LESS THAN (4),
          SUBPARTITION P4 VALUES LESS THAN (5),
          SUBPARTITION P5 VALUES LESS THAN (6),
          SUBPARTITION P6 VALUES LESS THAN (7),
          SUBPARTITION P7 VALUES LESS THAN (8),
          SUBPARTITION P8 VALUES LESS THAN (9),
          SUBPARTITION P9 VALUES LESS THAN (10),
          SUBPARTITION P10 VALUES LESS THAN (11),
          SUBPARTITION P11 VALUES LESS THAN (12),
          SUBPARTITION P12 VALUES LESS THAN (13)),
    PARTITION year_2013
       VALUES LESS THAN (2013)
       (
          SUBPARTITION p2013_1 VALUES LESS THAN (2),
          SUBPARTITION P2013_2 VALUES LESS THAN (3),
          SUBPARTITION P2013_3 VALUES LESS THAN (4),
          SUBPARTITION P2013_4 VALUES LESS THAN (5),
          SUBPARTITION P2013_5 VALUES LESS THAN (6),
          SUBPARTITION P2013_6 VALUES LESS THAN (7),
          SUBPARTITION P2013_7 VALUES LESS THAN (8),
          SUBPARTITION P2013_8 VALUES LESS THAN (9),
          SUBPARTITION P2013_9 VALUES LESS THAN (10),
          SUBPARTITION P2013_10 VALUES LESS THAN (11),
          SUBPARTITION P2013_11 VALUES LESS THAN (12),
          SUBPARTITION P2013_12 VALUES LESS THAN (13)));

Check the suitability of the online redefinition process for the relevant table.

BEGIN
   SYS.DBMS_REDEFINITION.can_redef_table (
      uname => 'ADURUOZ',
      tname => 'DEMO1',
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

Start the necessary process.

BEGIN
   SYS.DBMS_REDEFINITION.start_redef_table (uname => 'ADURUOZ',
                                            origin_table => 'DEMO1',
                                            int_table => 'DEMO');
END;
/

ensure that the data in the tables is synchronized.

BEGIN
   DBMS_REDEFINITION.sync_interim_table (uname => 'ADURUOZ',
                                         origin_table => 'DEMO1',
                                         int_table => 'DEMO');
END;
/

ensure that the online redefinition process is completed.

BEGIN
   DBMS_REDEFINITION.finish_redef_table (uname => 'ADURUOZ',
                                         origin_table => 'DEMO1',
                                         int_table => 'DEMO');
END;
/

After these steps, our non-partitioned DEMO1 table has been transformed into the structure of the DEMO table we created as partitioned. The non-existent columns have been added and the data has been arranged as partition and subpartition. As for our DEMO table, we can see that our DEMO1 table has been transformed into its old form.

SQL> desc ADURUOZ.DEMO;
 Name Null? Type
 ----------------------------------------- -------- - ---------------------------
 ID NOT NULL NUMBER(8)
 T_TASK DATE
 TASK VARCHAR2(255)

SQL> desc ADURUOZ.DEMO1;
 Name Null? Type
 ----------------------------------------- -------- - ---------------------------
 ID NUMBER(8)
 T_TASK DATE
 TASK VARCHAR2(255)
 PARTITION_YEAR NUMBER
 PARTITION_MONTH NUMBER