On Thu, 4 May 2000, [iso-8859-1] paquette stephane wrote:
> 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')
Don't you actually mean values less than to_date ('31-MAR-2001 00:00:00','D=
D-MON-YYYY HH24:MM:SS') ?
Jared
Otherwise
>=20
>=20
>=20
> --- "Elliott, Patrick" <Patrick.Elliott_at_bestbuy.com> a
> =E9crit:
> > Here is the correct syntax.
> >=20
> > 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;
> >=20
> > 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;
> >=20
> > You may need storage clauses on the add partition
> > statements if the default
> > storage for the tablespaces isn't correct.
> > > -----Original Message-----
> > > From:=09Charlie Mengler [SMTP:charliem_at_mwh.com]
> > > Sent:=09Wednesday, May 03, 2000 1:18 PM
> > > To:=09Multiple recipients of list ORACLE-L
> > > Subject:=09"Extending" partitioned tables
> > >=20
> > > If I were to pre-create today a partitioned table
> > using the SQL below
> > >=20
> > > create table sales_summary
> > > as select * from sales_summary_at_edwdev
> > > where 1 =3D 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,
> > > )
> > > /
> > >=20
> > > (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?
> > > =20
> > > create table sales_summary
> > > as select * from sales_summary_at_edwdev
> > > where 1 =3D 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,
> > > )
> > > /
> > > --=20
> > > Charlie Mengler Maintenance
> > Warehouse =20
> > > charliem_at_mwh.com 5505
> > Morehouse Drive =20
> > > 858-552-6229 San Diego,
> > CA 92121 =20
> > > If you don't use vi, then you shouldn't be working
> > on UNIX!
> > > --=20
> > > Author: Charlie Mengler
> > > INET: charliem_at_mwh.com
> > >=20
> > > Fat City Network Services -- (858) 538-5051=20
> > 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).
> > --=20
> > Author: Elliott, Patrick
> > INET: Patrick.Elliott_at_bestbuy.com
> >=20
> > 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).
>=20
> =3D=3D=3D=3D=3D
> Stephane Paquette
> DBA Oracle
> stephane_paquette_at_yahoo.com
> spaquette_at_houra.fr
> (33) 01 53 93 06 50
>=20
> __________________________________________________
> Do You Yahoo!?
> Send instant messages & get email alerts with Yahoo! Messenger.
> http://im.yahoo.com/
> --=20
> Author: =3D?iso-8859-1?q?paquette=3D20stephane?=3D
> INET: stephane_paquette_at_yahoo.com
>=20
> 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).
>=20
Jared Still
Received on Fri May 05 2000 - 11:34:06 CDT
