Adding Table Partition in Oracle

Here we will see examples to add partition in all different types of partitioning methods.

1.Add Partition in Range-Partitioned table 

SQL> ALTER TABLE TRANSACTIONS ADD PARTITION Y_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY'));

Table altered.

2.Add Partition in Hash-Partitioned table

SQL> SELECT TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='TRAN_HASH';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TRAN_HASH                      SYS_P121
TRAN_HASH                      SYS_P122
TRAN_HASH                      SYS_P123
TRAN_HASH                      SYS_P124
TRAN_HASH                      SYS_P125

5 rows selected.

SQL> ALTER TABLE TRAN_HASH ADD PARTITION SYS_P126;

Table altered.

SQL>  SELECT TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='TRAN_HASH';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TRAN_HASH                      SYS_P121
TRAN_HASH                      SYS_P122
TRAN_HASH                      SYS_P123
TRAN_HASH                      SYS_P124
TRAN_HASH                      SYS_P125
TRAN_HASH                      SYS_P126

6 rows selected.

3.Add Partition in List-partitioned table

SQL> SELECT TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
DEPT_LIST                      P1
DEPT_LIST                      P2

SQL> ALTER TABLE DEPT_LIST ADD PARTITION P3 VALUES ('DBA','QA');

Table altered.

SQL> SELECT TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
DEPT_LIST                      P1
DEPT_LIST                      P2
DEPT_LIST                      P3

SQL>

4.Add Partition in Interval-Partitioned table

We can’t simply add partition in interval-partitioned table. But to add partition we can LOCK the last partition that will add one partition in table.

Let’s see how :

SQL> ALTER TABLE TRAN_INTERVAL ADD PARTITION P7 VALUES LESS THAN (TO_DATE('01-07-2019','DD-MM-YYYY'));
ALTER TABLE TRAN_INTERVAL ADD PARTITION P7 VALUES LESS THAN (TO_DATE('01-07-2019','DD-MM-YYYY'))
            *
ERROR at line 1:
ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects

SQL>  select partition_name, high_value from dba_tab_partitions where table_name='TRAN_INTERVAL';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P1                             TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2                             TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3                             TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4                             TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P5                             TO_DATE(' 2019-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P6                             TO_DATE(' 2019-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL>  LOCK TABLE TRAN_INTERVAL PARTITION FOR (TO_DATE('01-06-2019','DD-MM-YYYY')) IN SHARE MODE;

Table(s) Locked.

SQL>  select partition_name, high_value from dba_tab_partitions where table_name='TRAN_INTERVAL';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P1                             TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2                             TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3                             TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4                             TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P5                             TO_DATE(' 2019-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P6                             TO_DATE(' 2019-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P126                       TO_DATE(' 2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected.

5. Add Partitions to a Composite *-Hash Partitioned Table

a.Range-Hash Partitioned table

SQL> ALTER TABLE TRANSACTIONS_MAIN_COMP_HASH ADD PARTITION Y_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')) SUBPARTITIONS 4;

Table altered.

b.List-Hash Partitioned table

SQL> ALTER TABLE DEPT_LIST_HASH ADD PARTITION P3 VALUES('DBA','QA') SUBPARTITIONS 4;

Table altered.

6. Add Partitions to a Composite *-List Partitioned Table
a.Range-List partitioned table

SQL> ALTER TABLE DEPT_RANGE_LIST ADD PARTITION P_R4 VALUES LESS THAN (45) (SUBPARTITION P_R4_L1 VALUES('DBA'),SUBPARTITION P_R4_L2 VALUES('QA'));

Table altered.

b.List-List partitioned table

SQL> ALTER TABLE DEPT_LIST_LIST ADD PARTITION P_L3 VALUES('MP') (SUBPARTITION P_L3_L1 VALUES('IN'));

Table altered.

7.Range-Range partitioned table

SQL> ALTER TABLE SUBSCRIPTION_DTL ADD PARTITION P_RR3 VALUES LESS THAN(TO_DATE('01-JUL-2019','DD-MON-YYYY')) (SUBPARTITION P_RR3_R1 VALUES LESS THAN (TO_DATE('31-JUL-2019','DD-MON-YYYY')) , SUBPARTITION P_RR3_R2 VALUES LESS THAN (TO_DATE('31-AUG-2019','DD-MON-YYYY')) , SUBPARTITION P_RR3_R3 VALUES LESS THAN (TO_DATE('30-SEP-2019','DD-MON-YYYY')));

Table altered.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp