CODEWITHSUNDEEP
oracle-databasepartitioningpartition
AYUSH SHARMA
AYUSH SHARMA

Reputation: 1

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

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.

Upvotes: 0

Views: 3486

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

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.

Upvotes: 0

Related Questions