How to modify a partition to add more than one sub Partition to this existing partition?

Question

I am trying to add sub Partitions to an existing partition but i am getting this error:

Oracle Error: ORA-14158

Error Description: Too many subpartition descriptions

Error Cause: CREATE TABLE or CREATE INDEX contained too many subpartition descriptions; maximum number of subpartitions is 1048575.

Action: Reduce number of subpartitions to not exceed 1024K-1.

If i try to add one sub partition to the existing it works with this query:

ALTER TABLE table_name MODIFY PARTITION partition_name ADD
SUBPARTITION subpartition_name VALUES  LESS THAN (TO_DATE('01-03-2018' , 'DD-MM-YYYY'));

But if i try to add more than one sub partition to this existing partition it gives the error mentioned above:

Here is the query for it:

ALTER TABLE table_name MODIFY PARTITION partition_name ADD
SUBPARTITION subpartition_name1 VALUES  LESS THAN (TO_DATE('01-03-2018' , 'DD-MM-YYYY')),
SUBPARTITION subpartition_name2 VALUES  LESS THAN (TO_DATE('01-04-2018' , 'DD-MM-YYYY'));

Even though i am not creating more than 1024K-1 subpartitions still i am getting this too many subpartitions description error.

Here is the Create Table Statement:

                **CREATE TABLE HTL_ALLOTMENT_TRACE (   

                    allotmentTraceID      NUMBER(19)            NOT NULL, 

                    organizationID        NUMBER(19)            NOT NULL,

                    locationID            NUMBER(10)            NOT NULL, 

                    traceBusinessDate     DATE                  NOT NULL                        
                )

                    PARTITION BY LIST (organizationID)

                    SUBPARTITION BY RANGE (traceBusinessDate)

                      (
                        PARTITION HALMTTRC_1 VALUES  (1)         
                      )**

If anyone has a suggestion please let me know.

CREATE TABLE HTL_ALLOTMENT_TRACE ( allotmentTraceID NUMBER(19) NOT NULL, organizationID NUMBER(19) NOT NULL, locationID NUMBER(10) NOT NULL, traceBusinessDate DATE NOT NULL ) PARTITION BY LIST (organizationID) SUBPARTITION BY RANGE (traceBusinessDate) ( PARTITION HALMTTRC_1 VALUES (1) ) — AYUSH SHARMA, Mar 16 '17 at 10:06
Please edit your question instead of putting it into comment - it is quite difficult to read. — Wernfried Domscheit, Mar 16 '17 at 10:10

score 0 · Answer 1 · answered Mar 16 '17 at 08:50

The limit of 1024K-1 partitions (or subpartitions) does not apply to the real number of partitions in your table. Oracle considers the full range of all possible partitions.

For example if you have a table like this

CREATE TABLE MY_TABLE
(
   END_TIME TIMESTAMP 
)
PARTITION BY RANGE (END_TIME)
INTERVAL (INTERVAL '1' DAY)
(
  PARTITION P_1 VALUES LESS THAN (TIMESTAMP '2017-03-01 00:00:00'),
  PARTITION P_2 VALUES LESS THAN (TIMESTAMP '2017-04-01 00:00:00')
);

Then Oracle would consider 31 partitions for limit of 1048575 subpartitions - although your table has only two partitions.

this issue i am only facing when doing modify partitions. If i create a new partition with subpartitions for 5 years also then it is working fine. Don't know where is the issue coming from. — AYUSH SHARMA, Mar 16 '17 at 09:20

How to modify a partition to add more than one sub Partition to this existing partition?

1 Answers1