How to create a partitioned table using a subpartition template

Using templates for your subpartitions is a handy shortcut to avoid specifying attributes for every subpartition in the table.

Oracle calls this concept "vertical striping", since it allows for each subpartition matching the template to end up in the same tablespace and thus allows you to "stripe" your partitions across multiple tablespaces, even though they logically belong to different partitions.

You only describe subpartitions once in the template; Oracle will then apply the template to every partition you create in the table.

The original table's layout is shown below. It is an imaginative tables that will store received documents in a governmental office.
In this example, the documents stored in the CLOB column will be saved in the same tablespace based on year, rather than the type of document it represents.

It will create

* A heap-organized, LIST-LIST partitioned table
* A column of type CLOB (character large object). We will call the CLOB object "REC_DOCS_XML_CLOB"
* 2 pre-defined LIST partitions
* 4 pre-defined LIST sub-partitions
* A subpartition template to simplify future additions of partitions.

CREATE TABLE RECEIVED_DOCUMENTS
(
  UUID                 VARCHAR2(160 BYTE),
  FISCAL_YEAR          NUMBER(4),
  DOCUMENTTYPE         VARCHAR2(100 CHAR),
  DOCUMENTNAME         VARCHAR2(1000 CHAR),
  DOCUMENTSTATE        VARCHAR2(30 CHAR),
  VALID                CHAR(1 BYTE),
  CREATED_TIMESTAMP    NUMBER(20),
  VERSION              NUMBER(20),
  DATA_XML             CLOB,
  FORMAT               VARCHAR2(1000 CHAR),
  PERIOD               VARCHAR2(1000 CHAR)
 )
LOB (DATA_XML) STORE AS SECUREFILE REC_DOCS_XML_CLOB(
 TABLESPACE RECEIVED_DOCUMENTS_LOB_DATA
 COMPRESS HIGH
)
-- The table definition is stored in the tablespace DOCS_DATA
TABLESPACE DOCS_DATA
PARTITION BY LIST (DOCUMENTTYPE)
SUBPARTITION BY LIST (PERIOD)
SUBPARTITION TEMPLATE
  (SUBPARTITION SP_2014 VALUES ('2014') TABLESPACE DOCS_2014,
   SUBPARTITION SP_2015 VALUES ('2015') TABLESPACE DOCS_2015,
   SUBPARTITION SP_2016 VALUES ('2016') TABLESPACE DOCS_2016,
   SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE DOCS_DATA
   )
(
  PARTITION SETTELM
    VALUES ('SETTLEM_ACCEPTED', 'SETTLEM_REJECTED'),
  PARTITION APPLICATIONS
    VALUES ('SINGLE_PARENT_SUPP','UNEMPLOYMENT_SUPP','CHILD_SUPP','HOUSING_SUPP')    
)
;

If you later would like to add a partition, it is as simple as

ALTER TABLE RECEIVED_DOCUMENTS
 ADD PARTITION APPLICANT VALUES ('FAMILY','SINGLE_PARENT','ASYLUM_SEEKER')
;

Similarly, dropping a partition with its subpartition would be done with:

ALTER TABLE RECEIVED_DOCUMENTS
DROP PARTITION APPLICATION;

Notice that if you have a default partition to handle incoming data that doesn't fit in any particular partition, you will get an error when attempting to add a partition.

Sources:

"Specifying Subpartition Templates to Describe Composite Partitioned Tables"

"Specifying a Subpartition Template for a *-List Partitioned Table"