关于oracle分区表

 

1)散列分区
create table emp_test
  (empno int,
   ename varchar2(20)
  )
  partition by hash(empno)
 (
   partition part1 tablespace tbs1,
   partition part2 tablespace tbs2 );

insert into emp_test values(1,'jack');
insert into emp_test values(2,'jack');
insert into emp_test values(3,'jack');
insert into emp_test values(4,'jack');
insert into emp_test values(5,'jack');
insert into emp_test values(6,'jack');
insert into emp_test values(7,'jack');
insert into emp_test values(8,'jack');
insert into emp_test values(9,'jack');
insert into emp_test values(10,'jack');
insert into emp_test values(11,'jack');
insert into emp_test values(12,'jack');
insert into emp_test values(13,'jack');
insert into emp_test values(14,'jack');
insert into emp_test values(15,'jack');
insert into emp_test values(16,'jack');

select * from emp_test partition(part1);

2)区间分区
create table range_example(
  msgid varchar2(31) not null,
  dt date,
  constraint pk_smsmsginfo primary key(msgid)
)
partition by range(dt)
(
   partition dt1 values less than (to_date('2009-01-01','yyyy-mm-dd')),
   partition dt2 values less than (to_date('2010-01-01','yyyy-mm-dd')),
   partition dt3 values less than (maxvalue)
);

3) 列表区间
  create table list_example
  (
     state_cd varchar2(2),
     data  varchar2(20)
  )
  partition by list(state_cd)
  (
     partition part1 values ('ME','NH'),
     partition part2 values('VA','NY')
  );

4) 间隔分区
 create table audit_trail
 (
    ts timestamp,
    data varchar2(30)
 )
 partition by range(ts)
 interval (numtoyminterval(1,'month'))
 store in(users, example)
 (partition p0 values less than (to_date('1900-01-01','yyyy-mm-dd')));

insert into audit_trail(ts,data) values(to_timestamp('2011-12-22','yyyy-mm-dd'), 'xx1');
insert into audit_trail values(to_timestamp('2011-10-22','yyyy-mm-dd'), 'xx2');
insert into audit_trail values(to_timestamp('2011-12-21','yyyy-mm-dd'), 'xx3');
insert into audit_trail values(to_timestamp('2011-12-01','yyyy-mm-dd'), 'xx3');

删除分操作以清除历史数据
alter table range_example drop partition dt1;
alter table range_example add partition dt201203 values less than (to_date('2012-03-01','yyyy-mm-dd'));
ALTER table list_example add partition  part3 values (default)
select a.partition_name,a.tablespace_name,a.high_value from user_tab_partitions A where A.table_name = 'AUDIT_TRAIL';