How to Add a New Partition to Range List Composite Partitioned Table Having Default MaxValue Partition (Doc ID 1389803.1)

Last updated on APRIL 28, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.2 and later
Information in this document applies to any platform.

Goal

The table has 2011 partitions, and you want to create the 2012 partitions.
The table has a default maxvalue partition, so you cannot just use ALTER TABLE ... ADD PARTITION statement to add a new partition to it because it results in ORA-14074.

e.g. the DDL of the table currently may look like this:


CREATE TABLE PARTITIO_TABLE ( "ID_TRAYECTO" NUMBER(6) NOT NULL , "ID_CALENDARIO" NUMBER(8) NOT NULL , "IFO_GESTION" NUMBER(6, 2) NOT NULL ,
"IFO_CLIENTE" NUMBER(6, 2) NOT NULL , "TRO_GESTION" NUMBER(6) NOT NULL , "TRO_CLIENTE" NUMBER(6) NOT NULL , "TPO_REAL" NUMBER(6) NOT NULL ,
"TPO_REAL_CLIENTE" NUMBER(6) NOT NULL , "TPO_PLAN" NUMBER(6) NOT NULL , "ID_LINEA" VARCHAR2(40 CHAR) NOT NULL
)
PARTITION BY RANGE ("ID_CALENDARIO") SUBPARTITION BY LIST ("ID_LINEA")
(
PARTITION "PTID_2011_12" VALUES LESS THAN (20120101)
(SUBPARTITION "PTID_2011_12_LINEA1" VALUES ('1'),
 SUBPARTITION "PTID_2011_12_LINEA2" VALUES ('2'),
 SUBPARTITION "PTID_2011_12_LINEA3" VALUES ('3'),
 SUBPARTITION "PTID_2011_12_LINEA4" VALUES ('4'),
 SUBPARTITION "PTID_2011_12_LINEA5" VALUES ('5'),
 SUBPARTITION "PTID_2011_12_LINEA6" VALUES ('6'),
 SUBPARTITION "PTID_2011_12_LINEA7" VALUES ('7'),
 SUBPARTITION "PTID_2011_12_LINEA8" VALUES ('8'),
 SUBPARTITION "PTID_2011_12_LINEA9" VALUES ('9'),
 SUBPARTITION "PTID_2011_12_LINEA10" VALUES ('10'),
 SUBPARTITION "PTID_2011_12_LINEA11" VALUES ('11'),
 SUBPARTITION "PTID_2011_12_LINEA12" VALUES ('12'),
 SUBPARTITION "PTID_2011_12_OTRAS" VALUES (DEFAULT)),
PARTITION "PTID_DEFAULT" VALUES LESS THAN (MAXVALUE)
(SUBPARTITION "PTID_DEFAULT_LINEA1" VALUES ('1'),
 SUBPARTITION "PTID_DEFAULT_LINEA2" VALUES ('2'),
 SUBPARTITION "PTID_DEFAULT_LINEA3" VALUES ('3'),
 SUBPARTITION "PTID_DEFAULT_LINEA4" VALUES ('4'),
 SUBPARTITION "PTID_DEFAULT_LINEA5" VALUES ('5'),
 SUBPARTITION "PTID_DEFAULT_LINEA6" VALUES ('6'),
 SUBPARTITION "PTID_DEFAULT_LINEA7" VALUES ('7'),
 SUBPARTITION "PTID_DEFAULT_LINEA8" VALUES ('8'),
 SUBPARTITION "PTID_DEFAULT_LINEA9" VALUES ('9'),
 SUBPARTITION "PTID_DEFAULT_LINEA10" VALUES ('10'),
 SUBPARTITION "PTID_DEFAULT_LINEA11" VALUES ('11'),
 SUBPARTITION "PTID_DEFAULT_LINEA12" VALUES ('12'),
 SUBPARTITION "PTID_DEFAULT_OTRAS" VALUES (DEFAULT)
 )
);

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms