Link to home
Start Free TrialLog 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
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 :-)