Home » RDBMS Server » Server Administration » Error when adding partitions to already partitioned table (Oracle,10.2.0.4,Linux)
Error when adding partitions to already partitioned table [message #348157] Mon, 15 September 2008 17:09 Go to next message
sheker2007
Messages: 19
Registered: November 2007
Junior Member
Hello,
I need help on partitioning.I have a table which is already partitioned(range) for each month from 2007 Jan to 2008 Dec.
I am trying to add 2006 year partitions to existing partiitons to load hystorical data.(But initially 2006 year partiions are not added.).Like this i have to add partitions for previous years upto year 2000.

getting below error :
ORA-14074: partition bound must collate higher than that of the last partition.

DDL to add 2006 year partiions :
Below code for only two months but i need to add for 12 months for 2006 year.

ALTER TABLE GST ADD PARTITION GST_FACT_JAN_2006
VALUES LESS THAN (20060201)
TABLESPACE DATA_01
STORAGE(MAXEXTENTS UNLIMITED);

ALTER TABLE GST ADD PARTITION GST_FACT_DEC_2006
VALUES LESS THAN (20070101)
TABLESPACE DATA_01
STORAGE(MAXEXTENTS UNLIMITED);

Existing DDL for my Table:
CREATE TABLE GST
(
SLS_DIM_ID NUMBER(38,0) NOT NULL,
BCH_DIM_ID NUMBER(38,0) NOT NULL,
LON_DIM_ID NUMBER(38,0) NOT NULL,
RPTG_PER_ID NUMBER(38,0) NOT NULL,
)
PARTITION BY RANGE(RPTG_PER_ID)
(PARTITION GST_FACT_JAN_2007 VALUES LESS THAN (20070201)
TABLESPACE DATA_01),
-------,
-------,
--------,
PARTITION GST_FACT_DEC_2008
VALUES LESS THAN (20090101)
TABLESPACE DATA_01
STORAGE(MAXEXTENTS UNLIMITED)
);
Re: Error when adding partitions to already partitioned table [message #348158 is a reply to message #348157] Mon, 15 September 2008 17:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ORA-14074: partition bound must collate higher than that of the last partition.
This error is self-explanatory.

New partition can only be added at the "top" of the list.
You can NOT add partition anywhere except at the TOP.

If the requirement is that 2006 data also be partitioned, then you need to CREATE a new partitioned table starting with January 2006 & move existing data into it.
Re: Error when adding partitions to already partitioned table [message #348160 is a reply to message #348158] Mon, 15 September 2008 17:23 Go to previous message
sheker2007
Messages: 19
Registered: November 2007
Junior Member
anacedent,

Thank you for quick reply.

Users want to add partitions on same existing
table.

Any other ways....

Regards,
Sheker
Previous Topic: summertime
Next Topic: Not able to drop tablespace
Goto Forum:
  


Current Time: Mon Nov 04 05:49:12 CST 2024