add partition to a LIST partitioned table

Waqasulhaq
Waqasulhaq used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Information Technology Specialist
Commented:
I think all you need is
ALTER TABLE MDLDBA.MDL_MPA_PAT_RX_PROF_SURRO_ID ADD PARTITION SUPP0411 VALUES (411) ...

Author

Commented:
nevermind. i got it
thanks

Author

Commented:
u were right awking00. Thats exactly what i did.
thanks
awking00Information Technology Specialist

Commented:
Glad you got it resolved :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial