With the DBMS_REDEFINITION package in the Oracle database, it is possible to modify the structure of the tables online. We can convert a non-partitioned table online into a partitioned structure with DBMS_REDEFINITION. The process steps will be as follows.
The following example specifies the steps required to convert a non-partitioned DEMO1 table to a partitioned DEMO table.
Primary key must be present in the table.
Create a non-partitioned table
Let’s create the table we want to partition with primary key.
|
1 2 3 4 5 6 7 |
CREATE TABLE demo1 ( id NUMBER (8), t_gorev DATE, gorev VARCHAR(255), CONSTRAINT redef_tab_pk PRIMARY KEY (id) ) |
Create a partitioned table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
CREATE TABLE demo ( id NUMBER (8), t_gorev DATE, gorev VARCHAR(255), partition_year AS (EXTRACT (YEAR FROM t_gorev)), 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 if online redefinition is possible for the table
|
1 2 3 4 5 6 7 |
BEGIN SYS.DBMS_REDEFINITION.can_redef_table ( uname => 'ADURUOZ', tname => 'DEMO1', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID); END; / |
Start the necessary process
|
1 2 3 4 5 6 |
BEGIN SYS.DBMS_REDEFINITION.start_redef_table (uname => 'ADURUOZ', orig_table => 'DEMO1', int_table => 'DEMO'); END; / |
Synchronize data in tables
|
1 2 3 4 5 6 |
BEGIN DBMS_REDEFINITION.sync_interim_table (uname => 'ADURUOZ', orig_table => 'DEMO1', int_table => 'DEMO'); END; / |
Complete the Process
|
1 2 3 4 5 6 |
BEGIN DBMS_REDEFINITION.finish_redef_table (uname => 'ADURUOZ', orig_table => 'DEMO1', int_table => 'DEMO'); END; / |
Check Table is Partitioned
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> desc ADURUOZ.DEMO; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(8) T_GOREV DATE GOREV VARCHAR2(255) SQL> desc ADURUOZ.DEMO1; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(8) T_GOREV DATE GOREV VARCHAR2(255) PARTITION_YEAR NUMBER PARTITITON_MONTH NUMBER |
