Oracle Partitioning - Add Partition

We can also add partitions to an already created partition table. Creating a partition table.
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (data_size)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (40) TABLESPACE DATA_04
);
Table created.

Note: The table should not have a max value partition, else you will need to use split partition instead of add partition. Partition can be added only in the end not in between.

SQL> insert into DATA_LOAD values (1,1,sysdate-20,9,'DL');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-9,20,'RJ');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-9,35,'LL');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into DATA_LOAD values (2,2,sysdate-3,50,'LL');
insert into DATA_LOAD values (2,2,sysdate-3,50,'LL')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Now when we tried to insert a value higher than the partition bound value we get this error.
Now lets add partition to accomodate this insert statement.

SQL> alter table DATA_LOAD add partition data_05_2009 VALUES LESS THAN (50) TABLESPACE DATA_01;
Table altered.

SQL> alter table DATA_LOAD add partition data_06_2009 VALUES LESS THAN (60) TABLESPACE DATA_02;
Table altered.

SQL> insert into DATA_LOAD values (2,2,sysdate-3,50,'LL');
1 row created.

Same way the partitions can be added for list or hash partition:
Hash partition: SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY HASH (source_id)
( PARTITION data_01_2009 TABLESPACE DATA_01,
PARTITION data_02_2009 TABLESPACE DATA_02,
PARTITION data_03_2009 TABLESPACE DATA_03,
PARTITION data_04_2009 TABLESPACE DATA_04
);
Table created.

SQL> alter table DATA_LOAD add partition data_05_2009 TABLESPACE DATA_01;
Table altered.


List Partition: SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
(PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA'),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA'),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA'),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND'),
PARTITION NO_REGION VALUES (NULL),
PARTITION OTHER VALUES (DEFAULT)
);
Table created.

Its important that the table does not have a DEFAULT value partition.

SQL> alter table DATA_LOAD add partition NORTH_EAST_01 VALUES ('ARUNACHAL PRADESH','MIZORAM','TRIPURA');
alter table DATA_LOAD add partition NORTH_EAST_01 VALUES ('ARUNACHAL PRADESH','MIZORAM','TRIPURA')
*
ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists


SQL> drop table DATA_LOAD
2 /
Table dropped.
Alternately, you cn also drop the partition, make sure that the partition does not have any data, rows:

SQL> alter table DATA_LOAD drop partition OTHER;
Table altered.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
(PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA'),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA'),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA'),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND'),
PARTITION NO_REGION VALUES (NULL)
);
Table created.

SQL> alter table DATA_LOAD add partition NORTH_EAST_01 VALUES ('ARUNACHAL PRADESH','MIZORAM','TRIPURA');
Table altered.

Add subpartition:

Create table first:
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20)
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (target_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (TO_DATE('15-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (TO_DATE('25-JAN-2009','DD-MON-YYYY'))
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (TO_DATE('15-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (TO_DATE('25-FEB-2009','DD-MON-YYYY'))
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (TO_DATE('15-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (TO_DATE('25-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (TO_DATE('15-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (TO_DATE('25-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.

SQL> alter table DATA_LOAD modify PARTITION data_01_2009 add SUBPARTITION data_01_2009_3 VALUES LESS THAN (TO_DATE('28-JAN-2009','DD-MON-YYYY'));
Table altered.

SQL> alter table DATA_LOAD modify PARTITION data_03_2009 add SUBPARTITION data_03_2009_4 VALUES LESS THAN (TO_DATE('28-MAR-2009','DD-MON-YYYY'));
alter table DATA_LOAD modify PARTITION data_03_2009 add SUBPARTITION data_03_2009_4 VALUES LESS THAN (TO_DATE('28-MAR-2009','DD-MON-YYYY'))
*
ERROR at line 1:
ORA-14211: subpartition bound must collate higher than that of the last subpartition

Cant add partition if there is a maxvalye partition. Please make sure there is no rows before dropping.

SQL> alter table DATA_LOAD drop SUBPARTITION data_03_2009_3;
Table altered.

SQL> alter table DATA_LOAD modify PARTITION data_03_2009 add SUBPARTITION data_03_2009_4 VALUES LESS THAN (TO_DATE('28-MAR-2009','DD-MON-YYYY'));
Table altered.

Details about Oracle Partition Methods/Types