Re: ORA-14316 table is not partitioned by List method

From: Andrea <netsecurity_at_tiscali.it>
Date: 30 Mar 2007 07:04:30 -0700
Message-ID: <1175263470.256778.30710@b75g2000hsg.googlegroups.com>

On 30 Mar, 15:37, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Andrea" <netsecur..._at_tiscali.it> wrote in message
>
> news:1175258561.593103.4500_at_n59g2000hsh.googlegroups.com...
>
>
>
> >I don't able to drop value for table partitioned:
>
> > ALTER TABLE HA_EVENT_P MODIFY PARTITION ENTRO_MARZO02 DROP VALUES
> > (TO_DATE('01-0-1/2005','DD-MM-YYYY'));
>
> > ORA-14316 table is not partitioned by List method
>
> > why??
>
> > SELECT HIGH_VALUE FROM USER_TAB_PARTITIONS;
>
> > HIGH_VALUE
> > --------------------------------------------------------------------------------
> > TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
> > 'NLS_CALENDAR=GREGORIAN'
> > MAXVALUE
>
> > :(
>
> It looks like the table is range partitioned.
> Try the following to check the DDL for
> the table:
>
> set long 20000
> select dbms_metadata.get_ddl('TABLE','HA_EVENT_P','TEST_USER') from dual
>
> Assumes you have execute privilege on dbms_metadata
> and suitable access to know about table HA_EVENT_P
> owned by user TEST_USER.
>

DBMS_METADATA.GET_DDL('TABLE','HA_EVENT_P','TEST')

---

  CREATE TABLE "TEST"."HA_EVENT_P"

   (    "EVENT_ID" NUMBER(11,0),
        "EMISSION_DATE" DATE,
        "EVENT_TYPE" NUMBER(4,0),
        "STR_EVENT_TYPE" VARCHAR2(16),
        "OBJECT" VARCHAR2(8),
        "OBJECT_DESCRIPTION" VARCHAR2(35),
        "STATUS_DESCRIPTION" VARCHAR2(35),
        "PARAMETER" VARCHAR2(32),
        "PARAMETER_2" VARCHAR2(32),

DBMS_METADATA.GET_DDL('TABLE','HA_EVENT_P','TEST')

---

        "PARAMETER_3" VARCHAR2(32),
        "PARAMETER_4" VARCHAR2(32),
        "PARAMETER_5" VARCHAR2(32),
        "RECEIVING_DATE" DATE,
        "COMPANY" VARCHAR2(8),
        "AGGREGATION_1" VARCHAR2(8),
        "AGGREGATION_2" VARCHAR2(8),
        "SITE" NUMBER(9,0),
        "SITE_ACRONYM" VARCHAR2(8)

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING   STORAGE(INITIAL 1835008 NEXT 663552 MINEXTENTS 1 MAXEXTENTS 2147483645

DBMS_METADATA.GET_DDL('TABLE','HA_EVENT_P','TEST')

---

  PCTINCREASE 50 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("EMISSION_DATE")
 (PARTITION "ENTRO_MARZO02" VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', '
SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255   STORAGE(INITIAL 1835008 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)   TABLESPACE "USERS" NOCOMPRESS ,
 PARTITION "DOPO_MARZO02" VALUES LESS THAN (MAXVALUE)   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 DBMS_METADATA.GET_DDL('TABLE','HA_EVENT_P','TEST')

---

  STORAGE(INITIAL 1835008 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)   TABLESPACE "USERS2" NOCOMPRESS ) Received on Fri Mar 30 2007 - 09:04:30 CDT