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

On Mar 30, 9:04 am, "Andrea" <netsecur..._at_tiscali.it> wrote:
> 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 )- Hide quoted text -
>
> - Show quoted text -

The table is, indeed, partitioned by range and, as such, you can drop the entire partition containing the desired date. You are attempting to drop values based up on a list partitioning scheme. This is why you receive the ORA-14316 error.

David Fitzjarrell Received on Fri Mar 30 2007 - 11:25:13 CDT