Partitioning -- 13b : Splitting a Partition

Let's say the business anticipates growing sales volume in 2019 and new reporting requirements.  IT analyses the requirements and decides that the SALES_DATA Table that is currently Partitioned by YEAR, needs to be Partitioned by HALF-YEAR from 2019 onwards.

SQL> select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P_2016                         ARCHIVE_SALES_DATA
P_2017                         TBS_YEAR_2017
P_2018                         TBS_YEAR_2018
P_2019                         TBS_YEAR_2019
P_2020                         TBS_YEAR_2020
P_MAXVALUE                     USERS

6 rows selected.

SQL> 
SQL> alter table sales_data
  2  split partition P_2019 at (to_date('01-JUL-2019','DD-MON-YYYY'))
  3  into (partition P_2019_H1, partition P_2019_H2)
  4  update indexes
  5  /

Table altered.

SQL> 
SQL> col high_value format a26 trunc
SQL> select partition_name, tablespace_name, high_value
  2  from user_tab_partitions
  3  where table_name  = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016                         ARCHIVE_SALES_DATA             TO_DATE(' 2017-01-01 00:00
P_2017                         TBS_YEAR_2017                  TO_DATE(' 2018-01-01 00:00
P_2018                         TBS_YEAR_2018                  TO_DATE(' 2019-01-01 00:00
P_2019_H1                      TBS_YEAR_2019                  TO_DATE(' 2019-07-01 00:00
P_2019_H2                      TBS_YEAR_2019                  TO_DATE(' 2020-01-01 00:00
P_2020                         TBS_YEAR_2020                  TO_DATE(' 2021-01-01 00:00
P_MAXVALUE                     USERS                          MAXVALUE

7 rows selected.

SQL> 

I used the UPDATE INDEXES clause to ensure that all (specifically Global) Indexes affected by the SPLIT are updated so that they don't go into an UNUSABLE state.

I could have optionally used a TABLESPACE clause for each of the two new Partitions P_2019_H1 and P_2019_H2


(Also see a previous BlogPost on using SPLIT PARTITION to add a new Partition at the "end" of the table by splitting the last Partition)