On 30 Mar, 18:25, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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.
>
but this method drops all data, i would preserve data and remake
values changed the date
Received on Tue Apr 03 2007 - 03:39:00 CDT
