RE: "Extending" partitioned tables

Here is the correct syntax.

alter table sales_summary add
 partition SS_2001Q1 values less than to_date('01-APR-2001','DD-MON-YYYY') tablespace SS_2001Q1;
alter table sales_summary add
 partition SS_2001Q2 values less than to_date('01-JUL-2001','DD-MON-YYYY') tablespace SS_2001Q2;
alter table sales_summary add
 partition SS_2001Q3 values less than to_date('01-JUL-2001','DD-MON-YYYY') tablespace SS_2001Q3;
alter table sales_summary add
 partition SS_2001Q4 values less than to_date('01-JAN-2002','DD-MON-YYYY') tablespace SS_2001Q4;

If you then need to drop the existing partitions, then you would do:

alter table sales_summary drop partition SS_2000Q1;
alter table sales_summary drop partition SS_2000Q2;
alter table sales_summary drop partition SS_2000Q3;
alter table sales_summary drop partition SS_2000Q4;

You may need storage clauses on the add partition statements if the default storage for the tablespaces isn't correct.
> -----Original Message-----
> From: Charlie Mengler [SMTP:charliem_at_mwh.com]
> Sent: Wednesday, May 03, 2000 1:18 PM
> To: Multiple recipients of list ORACLE-L
> Subject: "Extending" partitioned tables
>
> If I were to pre-create today a partitioned table using the SQL below
>
> create table sales_summary
> as select * from sales_summary_at_edwdev
> where 1 = 2
> partition by range (SALES_DATE)
> (partition SS_2000Q1 values less than
> to_date('01-APR-2000','DD-MON-YYYY') tablespace SS_2000Q1,
> partition SS_2000Q2 values less than
> to_date('01-JUL-2000','DD-MON-YYYY') tablespace SS_2000Q2,
> partition SS_2000Q3 values less than
> to_date('01-JUL-2000','DD-MON-YYYY') tablespace SS_2000Q3,
> partition SS_2000Q4 values less than
> to_date('01-JAN-2001','DD-MON-YYYY') tablespace SS_2000Q4,
> )
> /
>
> (Assume that the four 2001 tablespaces are created independently.)
> Would the following SQL allow me to "extend" the existing partitioned
> table made with the SQL above?
> If not, what SQL would be the needed next year to handle 2001 data in a
> similar manner?
>
> create table sales_summary
> as select * from sales_summary_at_edwdev
> where 1 = 2
> partition by range (SALES_DATE)
> (partition SS_2001Q1 values less than
> to_date('01-APR-2001','DD-MON-YYYY') tablespace SS_2001Q1,
> partition SS_2001Q2 values less than
> to_date('01-JUL-2001','DD-MON-YYYY') tablespace SS_2001Q2,
> partition SS_2001Q3 values less than
> to_date('01-JUL-2001','DD-MON-YYYY') tablespace SS_2001Q3,
> partition SS_2001Q4 values less than
> to_date('01-JAN-2002','DD-MON-YYYY') tablespace SS_2001Q4,
> )
> /
> --
> Charlie Mengler Maintenance Warehouse
> charliem_at_mwh.com 5505 Morehouse Drive
> 858-552-6229 San Diego, CA 92121
> If you don't use vi, then you shouldn't be working on UNIX!
> --
> Author: Charlie Mengler
> INET: charliem_at_mwh.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Wed May 03 2000 - 18:36:26 CDT