When using a date field as the upper bound of a
partition, you should specify the hour, minutes and
seconds as in
partition xxx values less than to_date ('01-APR-2001
23:59:59','DD-MON-YYYY HH24:MM:SS')
- "Elliott, Patrick" <Patrick.Elliott_at_bestbuy.com> a
icrit:
> 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
> > also send the HELP command for other information
> (like subscribing).
> --
> Author: Elliott, Patrick
> INET: Patrick.Elliott_at_bestbuy.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
> also send the HELP command for other information
> (like subscribing).
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
spaquette_at_houra.fr
(33) 01 53 93 06 50
Received on Thu May 04 2000 - 02:51:39 CDT
