oracle数据表分区，增删改查

create table TEST_PART_RANGE
(
  A1 NUMBER not null,
  A2 DATE      not null,
  A3 VARCHAR2(6) not null,
  A4 DATE not null,
  A5 NUMBER not null
)
partition by range (A1)
(
  partition P1 values less than (1000),
  partition P2 values less than (2000),
  partition P3 values less than (3000),
  partition P4 values less than (4000),
  partition P5 values less than (5000),
  partition P10 values less than (MAXVALUE)
);

select * from TEST_PART_RANGE;

select count(1) from TEST_PART_RANGE;
-- 添加数据
insert into TEST_PART (A1,A2,A3,A4,A5) values(123, to_date('2015-01-01','yyyy-mm-dd'),12345,to_date('2015-01-01','yyyy-mm-dd'), 123);
insert into TEST_PART (A1,A2,A3,A4,A5) values(456, to_date('2015-01-01','yyyy-mm-dd'),12345,to_date('2015-01-01','yyyy-mm-dd'), 1);
insert into TEST_PART (A1,A2,A3,A4,A5) values(1, to_date('2015-01-01','yyyy-mm-dd'),12345,to_date('2015-01-01','yyyy-mm-dd'), 2);
insert into TEST_PART (A1,A2,A3,A4,A5) values(2, to_date('2015-01-01','yyyy-mm-dd'),12345,to_date('2015-01-01','yyyy-mm-dd'), 3);
insert into TEST_PART (A1,A2,A3,A4,A5) values(1234, to_date('2015-01-01','yyyy-mm-dd'),12345,to_date('2015-01-01','yyyy-mm-dd'), 4);
insert into TEST_PART (A1,A2,A3,A4,A5) values(1111, to_date('2015-01-01','yyyy-mm-dd'),12345,to_date('2015-01-01','yyyy-mm-dd'), 5);
insert into TEST_PART (A1,A2,A3,A4,A5) values(2222, to_date('2015-01-01','yyyy-mm-dd'),12345,to_date('2015-01-01','yyyy-mm-dd'), 6);
insert into TEST_PART (A1,A2,A3,A4,A5) values(3333, to_date('2015-01-01','yyyy-mm-dd'),12345,to_date('2015-01-01','yyyy-mm-dd'), 7);
insert into TEST_PART (A1,A2,A3,A4,A5) values(3333, to_date('2015-01-01','yyyy-mm-dd'),12345,to_date('2015-01-01','yyyy-mm-dd'), 8);




-- 新增表分区字段，先删除MAXVALUE分区，再添加新增的分区字段，再执行MAXVALUE

ALTER TABLE TEST_PART_RANGE  ADD partition P8 values less than (10000);
-- 新增最大分区
ALTER TABLE TEST_PART_RANGE  ADD partition P11 values less than (MAXVALUE)

-- 删除表中分区字段
ALTER TABLE TEST_PART_RANGE DROP PARTITION P10;

-- 重命名一个分区
ALTER TABLE TEST_PART_RANGE RENAME PARTITION P4 TO PP4
-- 查询表有多少个分区
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TEST_PART';