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 |
