Subpartition template

Use a subpartition template to automatically create subpartitions.

–Create new table NEW_TABLE if this has not already happened
DECLARE
table_exists NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO table_exists FROM USER_TABLES WHERE TABLE_NAME = ‘NEW_TABLE’;
IF table_exists = 0 THEN

EXECUTE IMMEDIATE(q'{
CREATE TABLE NEW_TABLE
(
EXT_SRC_SYSTEM VARCHAR2(12 CHAR),
EXT_PERIOD_CODE VARCHAR2(1 CHAR),
BUSINESS_DATE VARCHAR2(8 CHAR),
CUSTOMER_CODE VARCHAR2(25 CHAR),
CUSTOMER_SOURCE_SYSTEM VARCHAR2(20 CHAR),
CUSTOMER_ATTRIBUTE_NAME VARCHAR2(100 CHAR),
CUSTOMER_ATTRIBUTE_VALUE VARCHAR2(50 CHAR)
)
PARTITION BY LIST (“EXT_SRC_SYSTEM”)
SUBPARTITION BY LIST (“EXT_PERIOD_CODE”)
SUBPARTITION TEMPLATE
(
SUBPARTITION D VALUES (‘D’),
SUBPARTITION M VALUES (‘M’),
SUBPARTITION Q VALUES (‘Q’)
)
(
PARTITION PAR1 VALUES (‘PAR1’) SEGMENT CREATION IMMEDIATE,
PARTITION PAR2 VALUES (‘PAR2’) SEGMENT CREATION IMMEDIATE,
PARTITION PAR3 VALUES (‘PAR3’) SEGMENT CREATION IMMEDIATE,
PARTITION PAR4 VALUES (‘PAR4’) SEGMENT CREATION IMMEDIATE,
PARTITION PAR5 VALUES (‘PAR5’) SEGMENT CREATION IMMEDIATE,
PARTITION PAR6 VALUES (‘PAR6′) SEGMENT CREATION IMMEDIATE
)
}’);

END IF;
END;
/