Link to home
Create AccountLog in
Avatar of Waqasulhaq
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_PROF_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_PROF_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.
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Waqasulhaq
Waqasulhaq

ASKER

nevermind. i got it
thanks
u were right awking00. Thats exactly what i did.
thanks
Glad you got it resolved :-)