http://hi.baidu.com/kywinder/blog/item/defdd3784bd200e12f73b33f.html

drop table sales_range purge;

创建范围partition表
--range partition
CREATE TABLE sales_range
(salesman_id  NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount  NUMBER(10),
sales_date    DATE)
COMPRESS
PARTITION BY RANGE(sales_date)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2010','MM/DD/YYYY')),
 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2010','MM/DD/YYYY')),
 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2010','MM/DD/YYYY')),
 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2010','MM/DD/YYYY')));

insert into sales_range values (1, 'Jacky1', 100, to_date('01/01/2010','DD/MM/YYYY'));
insert into sales_range values (1, 'Jacky2', 100, to_date('03/03/2010','DD/MM/YYYY'));
insert into sales_range values (1, 'Jacky3', 100, to_date('04/03/2010','DD/MM/YYYY'));
insert into sales_range values (1, 'Jacky4', 100, to_date('05/03/2010','DD/MM/YYYY'));

drop index sales_ix1;
创建本地分区索引
CREATE INDEX sales_ix1 ON sales_range(sales_date)
LOCAL
      (PARTITION sales_ix_jan2000 ,
 PARTITION sales_ix_feb2000 ,
 PARTITION sales_ix_mar2000 ,
 PARTITION sales_ix_apr2000
 );

创建本地非前缀分区索引
CREATE INDEX sales_name_ix ON sales_range(sales_date, salesman_name)
LOCAL
      (PARTITION sales_name_ix_jan2000 ,
 PARTITION sales_name_ix_feb2000 ,
 PARTITION sales_name_ix_mar2000 ,
 PARTITION sales_name_ix_apr2000
 );

创建全局前缀分区索引
CREATE INDEX sales_id_ix ON sales_range(sales_date,salesman_id)
GLOBAL PARTITION BY RANGE(sales_date)
      (PARTITION sales_id_ix_jan2000 VALUES LESS THAN(TO_DATE('01/01/2010','MM/DD/YYYY')),
 PARTITION sales_id_ix_feb2000 VALUES LESS THAN(TO_DATE('02/01/2010','MM/DD/YYYY')),
 PARTITION sales_id_ix_mar2000 VALUES LESS THAN(TO_DATE('03/01/2010','MM/DD/YYYY')),
 PARTITION sales_id_ix_apr2000 VALUES LESS THAN(TO_DATE('04/01/2010','MM/DD/YYYY')),
 PARTITION sales_id_ix_max VALUES LESS THAN(MAXVALUE)
 );

查看索引分区
select index_name,partition_name from user_ind_partitions;

创建hash partition

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
     STORAGE (INITIAL 10K)
     PARTITION BY HASH(deptno)
       (PARTITION p1 , PARTITION p2 ,
        PARTITION p3 , PARTITION p4 );

创建本地分区索引
CREATE INDEX dept_ix1 ON dept(deptno)
LOCAL
      (PARTITION dept_p1 ,
 PARTITION dept_p2,
 PARTITION dept_p3,
 PARTITION dept_p4
 );

创建list partition
CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER,
             store_name VARCHAR(30), state_code VARCHAR(2),
             sale_date DATE)
     PARTITION BY LIST (state_code)
     (
     PARTITION region_east
        VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ'),
     PARTITION region_west
        VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO'),
     PARTITION region_south
        VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
     PARTITION region_central
        VALUES ('OH','ND','SD','MO','IL','MI','IA'),
     PARTITION region_null
        VALUES (NULL),
     PARTITION region_unknown
        VALUES (DEFAULT)
     );

select table_name,partition_name from user_tab_partitions;

创建本地分区索引
CREATE INDEX sales_by_region_ix ON sales_by_region(state_code)
LOCAL
      (PARTITION sales_by_region_p1 ,
 PARTITION sales_by_region_p2,
 PARTITION sales_by_region_p3,
 PARTITION sales_by_region_p4,
 PARTITION sales_by_region_p5,
 PARTITION sales_by_region_p6
 );

创建全局索引
create index alses_by_reigion_gix on sales_by_region(item#) global;

创建本地分区非前缀索引
create index alses_by_reigion_g1ix on sales_by_region(item#) local
(
PARTITION  sales_by_region_p_p1 ,
 PARTITION sales_by_region_p_p2,
 PARTITION sales_by_region_p_p3,
 PARTITION sales_by_region_p_p4,
 PARTITION sales_by_region_p_p5,
 PARTITION sales_by_region_p_p6
)
;

增加add partition
ALTER TABLE sales_range
      ADD PARTITION sales_May2000 VALUES LESS THAN ( TO_DATE('06/01/2010','MM/DD/YYYY') )
;

ALTER TABLE dept
      ADD PARTITION p_named;

--not work
ALTER TABLE sales_by_region
   ADD PARTITION region_eastsouth VALUES ('HA', 'HB')
;

alter table sales_by_region
 split partition region_unknown values('HA', 'HB') into (
  partition region_eastsouth,
  partition region_unknown
 )
;

删除drop partition
ALTER TABLE sales_range DROP PARTITION sales_May2000
     UPDATE INDEXES;
select index_name, partition_name,status from user_ind_partitions;

交换exchange partition
CREATE TABLE sales_bak
(salesman_id  NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount  NUMBER(10),
sales_date    DATE)
;

create index sales_bak_ix on sales_bak(salesman_name);

delete from sales_bak;
insert into sales_bak values (1, 'Jacky3', 100, to_date('05/23/2010','MM/DD/YYYY'));
commit;

ALTER TABLE sales_range
      ADD PARTITION sales_May2000 VALUES LESS THAN ( TO_DATE('06/01/2010','MM/DD/YYYY') )
;

ALTER TABLE sales_range
    EXCHANGE PARTITION sales_May2000 WITH TABLE sales_bak;

合并merge partition
ALTER TABLE sales_range
   MERGE PARTITIONS sales_jan2000, sales_feb2000
   INTO PARTITION sales_feb2000 update indexes;

重新build index
select 'alter index '||index_name || ' rebuild partition ' || partition_name || ';' from user_ind_partitions
where status != 'USABLE';

select index_name, partition_name, status from user_ind_partitions
;

修改属性modify default attribute

ALTER TABLE dept
     MODIFY DEFAULT ATTRIBUTES TABLESPACE users;

增加value到list partition
Alter table sales_by_region modify partition region_east add values('NN');

删除value DROP VALUES
Alter table sales_by_region modify partition region_east drop values('NN');

分裂split partition
Alter table sales_range split partition sales_feb2000 at (to_date('02/01/2010','MM/DD/YYYY'))
into (partition sales_jan2000,partition sales_feb2000) update indexes;

截除truncate partition
Alter table dept truncate partition p1 update indexes;

更改表空间move partition tablespace
ALTER TABLE sales_by_region MOVE PARTITION region_east
     TABLESPACE users NOLOGGING COMPRESS update indexes;

重命名rename partition

Alter table sales_by_region rename partition region_east to region_east_new;

Alter table sales_by_region rename partition region_east_new to region_east;