如下是已经建好的分区表,如何修改为分区:PARTITION BY RANGE (COLLECTDATE) subpartition by hash(EQUIP_ID,IFINDEX)(
....
TABLE NETWORK_FLOWTABLE_TEST
(
EQUIP_ID NUMBER(20) NOT NULL,
IFINDEX NUMBER(10),
COLLECTDATE DATE,
RXUTIL NUMBER(20,4),
TXUTIL NUMBER(20,4),
RXBPS NUMBER(20,4),
TXBPS NUMBER(20,4),
CLEANID1 NUMBER(20),
CLEANID2 NUMBER(20),
CLEANID3 NUMBER(20),
PERIOD NUMBER(4) DEFAULT '0'
)PARTITION BY RANGE (COLLECTDATE) subpartition by hash(EQUIP_ID)(
PARTITION VALUES LESS THAN (TO_DATE('2012-01-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-02-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-03-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-04-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-05-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-06-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-07-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-08-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-09-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-10-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-11-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-12-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-02-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-03-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-04-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-05-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-06-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-07-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-08-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-09-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-10-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-11-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-12-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-02-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-03-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-04-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-05-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-06-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-07-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-08-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-09-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-10-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-11-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-12-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-02-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-03-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-04-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-05-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-06-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-07-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-08-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-09-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-10-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-11-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-12-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-02-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-03-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-04-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-05-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-06-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-07-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-08-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-09-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-10-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-11-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-12-01','YYYY-MM-DD'))
); 分区
....
TABLE NETWORK_FLOWTABLE_TEST
(
EQUIP_ID NUMBER(20) NOT NULL,
IFINDEX NUMBER(10),
COLLECTDATE DATE,
RXUTIL NUMBER(20,4),
TXUTIL NUMBER(20,4),
RXBPS NUMBER(20,4),
TXBPS NUMBER(20,4),
CLEANID1 NUMBER(20),
CLEANID2 NUMBER(20),
CLEANID3 NUMBER(20),
PERIOD NUMBER(4) DEFAULT '0'
)PARTITION BY RANGE (COLLECTDATE) subpartition by hash(EQUIP_ID)(
PARTITION VALUES LESS THAN (TO_DATE('2012-01-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-02-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-03-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-04-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-05-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-06-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-07-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-08-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-09-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-10-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-11-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2012-12-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-02-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-03-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-04-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-05-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-06-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-07-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-08-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-09-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-10-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-11-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2013-12-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-02-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-03-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-04-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-05-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-06-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-07-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-08-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-09-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-10-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-11-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2014-12-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-02-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-03-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-04-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-05-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-06-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-07-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-08-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-09-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-10-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-11-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2015-12-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-02-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-03-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-04-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-05-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-06-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-07-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-08-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-09-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-10-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-11-01','YYYY-MM-DD')),
PARTITION VALUES LESS THAN (TO_DATE('2016-12-01','YYYY-MM-DD'))
); 分区
create table NETWORK_FLOWTABLE_TEST (EQUIP_ID not null,PERIOD DEFAULT '0')
parallel 32 nologging
PARTITION BY RANGE (COLLECTDATE) subpartition by hash(EQUIP_ID,IFINDEX)(
....
)
as
select * from NETWORK_FLOWTABLE_TEST_r;
drop table NETWORK_FLOWTABLE_TEST_r purge;
alter table NETWORK_FLOWTABLE_TEST noparallel;
1.重建一张表,export/import
2.交换分区,重建一张分区表和普通表
3.在线重定义