CODEWITHSUNDEEP
sqloracle-database
Pravin Satav
Pravin Satav

Reputation: 702

Syntax for partition with subpartition

col1 ....
col2 ....
col3 ....
col4 ....
PARTITION BY RANGE(DATE_KEY)
SUBPARTITION BY LIST(REGION_KEY)
SUBPARTITION TEMPLATE
  (SUBPARTITION A VALUES (1) TABLESPACE X,
   SUBPARTITION B VALUES(3) TABLESPACE X,
   SUBPARTITION C VALUES (8) TABLESPACE X)
(PARTITION MON_JAN_2012 VALUES LESS THAN (1000)
    TABLESPACE X
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    NOLOGGING
  (SUBPARTITION A VALUES (1) TABLESPACE X,
   SUBPARTITION B VALUES(3) TABLESPACE X,
   SUBPARTITION C VALUES (8) TABLESPACE X),
 PARTITION MON_FEB_2012 VALUES LESS THAN (2000)
    TABLESPACE X        PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    NOLOGGING
  (SUBPARTITION A VALUES (1) TABLESPACE X,
   SUBPARTITION B VALUES(3) TABLESPACE X,
   SUBPARTITION C VALUES (8) TABLESPACE X)

My table structure is something like this, I m new to partitions, need to add new partitions for table from March_2021 for few months, need correct syntax for this, I tried to google but not able to find example for adding partition with subpartition. Database 10g

Upvotes: 0

Views: 8163

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

Because you have a template, adding partitions is transparent to subpartitions:

alter table your_table add partition mon_mar_2012 values less than (3000);

(This will automaticaly creates subpartitions for the new partition).

EDIT: if you wouldn't have had template, you should have create subpartitions manualy:

ALTER TABLE your_table MODIFY PARTITION partition
      ADD SUBPARTITION subpartition_name ...

Upvotes: 1

Related Questions