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.
新闻热点
疑难解答
