sql> select partitioned from dba_tables where table_name='lytusage';

par
---
yes


sql> select partition_name,high_value from dba_tab_partitions where table_name='lytusage';

partition_name                 high_value
------------------------------ --------------------------------------------------------------------------------
lytusage_200401                to_date(' 2004-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200402                to_date(' 2004-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200403                to_date(' 2004-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200404                to_date(' 2004-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200405                to_date(' 2004-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200406                to_date(' 2004-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200407                to_date(' 2004-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200408                to_date(' 2004-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200409                to_date(' 2004-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200410                to_date(' 2004-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200411                to_date(' 2004-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200412                to_date(' 2005-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200501                to_date(' 2005-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200502                to_date(' 2005-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200503                to_date(' 2005-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200504                to_date(' 2005-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200505                to_date(' 2005-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200506                to_date(' 2005-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200507                to_date(' 2005-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200508                to_date(' 2005-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200509                to_date(' 2005-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200510                to_date(' 2005-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200511                to_date(' 2005-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200512                to_date(' 2006-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria

24 rows selected.

刪除2005年1月和2月的分區：
sql> alter table lytusage drop partition lytusage_200501;

table altered.

sql> alter table lytusage drop partition lytusage_200502;

table altered.

sql> select partition_name,high_value,tablespace_name from dba_tab_partitions where table_name='lytusage';

partition_name                 high_value
------------------------------ --------------------------------------------------------------------------------
tablespace_name
------------------------------

lytusage_200401                to_date(' 2004-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200402                to_date(' 2004-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200403                to_date(' 2004-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200404                to_date(' 2004-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200405                to_date(' 2004-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200406                to_date(' 2004-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200407                to_date(' 2004-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200408                to_date(' 2004-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200409                to_date(' 2004-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200410                to_date(' 2004-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200411                to_date(' 2004-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200412                to_date(' 2005-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200503                to_date(' 2005-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200504                to_date(' 2005-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200505                to_date(' 2005-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200506                to_date(' 2005-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200507                to_date(' 2005-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200508                to_date(' 2005-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200509                to_date(' 2005-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200510                to_date(' 2005-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200511                to_date(' 2005-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos

lytusage_200512                to_date(' 2006-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
wacos


22 rows selected.


sql> alter table lytusage
split partition lytusage_200503 at (to_date('2005-03-01 00:00:00','syyyy-mm-dd hh24:mi:ss'))
into (partition lytusage_200502 tablespace wacos,partition lytusage_200503 tablespace wacos) update global indexes;

table altered.

sql> alter table lytusage
split partition lytusage_200502 at (to_date('2005-02-01 00:00:00','syyyy-mm-dd hh24:mi:ss'))
into (partition lytusage_200501 tablespace wacos,partition lytusage_200502 tablespace wacos) update global indexes;

table altered.

partition_name                 high_value
------------------------------ --------------------------------------------------------------------------------
lytusage_200412                to_date(' 2005-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200401                to_date(' 2004-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200402                to_date(' 2004-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200403                to_date(' 2004-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200404                to_date(' 2004-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200405                to_date(' 2004-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200406                to_date(' 2004-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200407                to_date(' 2004-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200408                to_date(' 2004-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200409                to_date(' 2004-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200410                to_date(' 2004-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200411                to_date(' 2004-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200503                to_date(' 2005-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200504                to_date(' 2005-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200505                to_date(' 2005-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200506                to_date(' 2005-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200507                to_date(' 2005-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200508                to_date(' 2005-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200509                to_date(' 2005-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200510                to_date(' 2005-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200511                to_date(' 2005-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200512                to_date(' 2006-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200501                to_date(' 2005-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria
lytusage_200502                to_date(' 2005-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregoria

24 rows selected.

sql> alter table lytusage drop partition lytusage_200511;

table altered.

sql> alter table lytusage drop partition lytusage_200512;

table altered.

sql> alter table lytusage add partition lytusage_200511
values less than (to_date('2005-12-01 00:00:00','syyyy-mm-dd hh24:mi:ss'))
tablespace wacos 
storage(
 initial 1m
 next 10m
 minextents 1
 maxextents unlimited
 pctincrease 0 )
 pctfree 5
 pctused 95
 nologging;

table altered.

alter table lytusage add partition lytusage_200512
values less than (to_date('2006-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss'))
tablespace wacos 
storage(
 initial 1m
 next 10m
 minextents 1
 maxextents unlimited
 pctincrease 0 )
 pctfree 5
 pctused 95
 nologging;

table altered.