Waqasulhaq
asked on
add partition to a LIST partitioned table
Hi,
I need to add a partition to a LIST partitioned table that is global hash partitioned as well. Below is the DDL for the table. Kindly help with the syntax:
DDL: (important portions of it)
CREATE TABLE "MDLDBA"."MDL_MPA_PAT_RX_P ROF_SURRO_ ID"
( "RX_SURRO_ID" NUMBER(15,0) NOT NULL ENABLE,
"SUPPLIER_ID" NUMBER(3,0) NOT NULL ENABLE,
"PAT_IMS_NBR" NUMBER(11,0) NOT NULL ENABLE,
"LNG_IMS_ID" NUMBER(10,0) NOT NULL ENABLE,
PRIMARY KEY ("RX_SURRO_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "TS_LRG_IND03" GLOBAL PARTITION BY HASH ("RX_SURRO_ID")
(PARTITION "PART01"
TABLESPACE "TS_LRG_IND03",
PARTITION "PART16"
TABLESPACE "TS_LRG_IND03") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "TS_LRG_TBL03"
PARTITION BY LIST ("SUPPLIER_ID")
(PARTITION "SUPP0000" VALUES (0)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_LRG_TBL03" NOCOMPRESS ,
PARTITION "SUPP0806" VALUES (806)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_LRG_TBL03" NOCOMPRESS )
I need to add partition SUPP0411.
I tried the following, but it gave me the below error:
ALTER TABLE "MDLDBA"."MDL_MPA_PAT_RX_P ROF_SURRO_ ID"
2 ADD PARTITION BY LIST ("SUPPLIER_ID")
3 (PARTITION "SUPP0411" VALUES (411) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_LRG_TBL03" NOCOMPRESS);
ADD PARTITION BY LIST ("SUPPLIER_ID")
*
ERROR at line 2:
ORA-00902: invalid datatype
Thank you.
I need to add a partition to a LIST partitioned table that is global hash partitioned as well. Below is the DDL for the table. Kindly help with the syntax:
DDL: (important portions of it)
CREATE TABLE "MDLDBA"."MDL_MPA_PAT_RX_P
( "RX_SURRO_ID" NUMBER(15,0) NOT NULL ENABLE,
"SUPPLIER_ID" NUMBER(3,0) NOT NULL ENABLE,
"PAT_IMS_NBR" NUMBER(11,0) NOT NULL ENABLE,
"LNG_IMS_ID" NUMBER(10,0) NOT NULL ENABLE,
PRIMARY KEY ("RX_SURRO_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "TS_LRG_IND03" GLOBAL PARTITION BY HASH ("RX_SURRO_ID")
(PARTITION "PART01"
TABLESPACE "TS_LRG_IND03",
PARTITION "PART16"
TABLESPACE "TS_LRG_IND03") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "TS_LRG_TBL03"
PARTITION BY LIST ("SUPPLIER_ID")
(PARTITION "SUPP0000" VALUES (0)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_LRG_TBL03" NOCOMPRESS ,
PARTITION "SUPP0806" VALUES (806)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_LRG_TBL03" NOCOMPRESS )
I need to add partition SUPP0411.
I tried the following, but it gave me the below error:
ALTER TABLE "MDLDBA"."MDL_MPA_PAT_RX_P
2 ADD PARTITION BY LIST ("SUPPLIER_ID")
3 (PARTITION "SUPP0411" VALUES (411) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_LRG_TBL03" NOCOMPRESS);
ADD PARTITION BY LIST ("SUPPLIER_ID")
*
ERROR at line 2:
ORA-00902: invalid datatype
Thank you.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
u were right awking00. Thats exactly what i did.
thanks
thanks
Glad you got it resolved :-)
ASKER
thanks