I had a LIST/RANGE partitioned tables that needed a new partition.
The table is partitioned BY LIST on column STREAM, and subpartitioned BY RANGE on column LASTUPDATED.
DDL for the table:
Each subpartition is placed in a tablespace according to the year in the LASTUPDATED column.
So for example, no matter which partition the subpartition belongs to, it will always go into tablespace DATA_2019 if the LASTUPDATED column has a value which falls within the boundaries of year 2019.
This is called vertically striping of partitions and is often, but now always, set up using a subpartition template.
DDL for the table:
CREATE TABLE CATEGORIES ( UID VARCHAR2(255 BYTE), CREATED TIMESTAMP(6) NOT NULL, LASTUPDATED TIMESTAMP(6) NOT NULL, BODY CLOB, STREAM VARCHAR2(255 BYTE) ) LOB (SBODY) STORE AS SECUREFILE BLOB ( TABLESPACE DATA1 ENABLE STORAGE IN ROW CHUNK 8192 COMPRESS MEDIUM ) COMPRESS FOR OLTP TABLESPACE DATA1 PARTITION BY LIST (STREAM) SUBPARTITION BY RANGE (LASTUPDATED) ( -- partition definition .. -- subpartition definition.. );I had the following SQL intended to split the partition OTHERS into a brand new partition called DIGITAL_MEDIA:
ALTER TABLE categories SPLIT PARTITION OTHERS VALUES ('audiobooks') INTO ( PARTITION DIGITAL_MEDIA TABLESPACE DATA1 ( -- 2016 SUBPARTITION SP_dm_older VALUES LESS THAN ( TO_DATE('2016-01', 'YYYY-MM')) TABLESPACE DATA1, subpartition SP_dm_201601 VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016, LOB (BLOB) STORE AS SECUREFILE ( TABLESPACE DATA_2016 ), VALUES LESS THAN (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016, LOB (BLOB) STORE AS SECUREFILE ( TABLESPACE DATA_2016 ), . . . subpartition SP_dm_202411 VALUES LESS THAN (TO_DATE(' 2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2024 LOB (BLOB) STORE AS SECUREFILE ( TABLESPACE ATOMHOPPER_2024 ), subpartition SP_dm_202412 VALUES LESS THAN (TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2024 LOB (BLOB) STORE AS SECUREFILE ( TABLESPACE DATA_2024 ), SUBPARTITION SP_DM_DEFAULT VALUES LESS THAN (MAXVALUE) TABLESPACE DATA1 LOB (BLOB) STORE AS SECUREFILE ( TABLESPACE DATA1) ) , PARTITION OTHERS ) ONLINE UPDATE GLOBAL INDEXES;There were a total of 110 subpartitioned listed in the above DDL. When trying to execute the script, I received:
ORA-14634: Subpartition descriptions cannot be specified during the SPLIT/MERGE of a partition of a Range-List partitioned tableLooking it up:
oerr ora 14634 // *Cause: During a split or a merge of a partition of a range list // partitioned table you cannot specify any subpartitioning // information for the resulting partition (s) // *Action: Remove all subpartitioning information from the DDL.A rather clear message, which I followed, and thus changed my DDL to this:
ALTER TABLE categories SPLIT PARTITION OTHERS VALUES ('audiobooks') INTO ( PARTITION DIGITAL_MEDIA , PARTITION OTHERS) ONLINE UPDATE GLOBAL INDEXES;indeed a much simpler syntax.
It parsed without errors, and the result was a new partition, with system-generated names, placed in the correct tablespaces.
Let's verify the results:
The fact that we for this particular partition now must deal with system generated names is of no importance. The optimizer will still use partition pruning when appropriate.
SELECT table_name,partition_name,subpartition_name,tablespace_name FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='CATEGORIES' AND PARTITION_NAME='DIGITAL_MEDIA';
TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | TABLESPACE_NAME |
---|---|---|---|
CATEGORIES | DIGITAL_MEDIA | SYS_SUBP39427 | DATA_2016 |
CATEGORIES | DIGITAL_MEDIA | SYS_SUBP39428 | DATA_2016 |
CATEGORIES | DIGITAL_MEDIA | SYS_SUBP39429 | DATA_2016 |
CATEGORIES | DIGITAL_MEDIA | SYS_SUBP39527 | DATA_2024 |
CATEGORIES | DIGITAL_MEDIA | SYS_SUBP39528 | DATA_2024 |
CATEGORIES | DIGITAL_MEDIA | SYS_SUBP39529 | DATA_2024 |