select path from v$datafile ;

–创建分区表
create TABLESPACE CNDBA1 datafile ‘/home/dmdba/dmdbms/data/DCP/CNDDBA1_01.DBF’ size 128 ;

create TABLESPACE CNDBA2 datafile ‘/home/dmdba/dmdbms/data/DCP/CNDDBA1_02.DBF’ size 128 ;

create TABLESPACE CNDBA3 datafile ‘/home/dmdba/dmdbms/data/DCP/CNDDBA1_03.DBF’ size 128 ;

create TABLESPACE CNDBA4 datafile ‘/home/dmdba/dmdbms/data/DCP/CNDDBA1_04.DBF’ size 128 ;

create user CNDBA IDENTIFIED by dameng123 default TABLESPACE CNDBA1 ;

grant public ,resource ,dba to cndba ;

–创建范围分区表

drop table IF EXISTS CNDBA.fanwei_t1 ;

create table CNDBA.fanwei_T1(
id int ,
name VARCHAR(20)
)
PARTITION BY RANGE (id)
(
PARTITION p1 VALUES LESS THAN (100) STORAGE( INITIAL 1 ,NEXT 1 ,MINEXTENTS 1 ,FILLFACTOR 0 , on CNDBA1), --99
PARTITION p2 VALUES LESS THAN (200) STORAGE( INITIAL 1 ,NEXT 1 ,MINEXTENTS 1 ,FILLFACTOR 0 , on CNDBA2), --199
PARTITION p3 VALUES LESS THAN (300) STORAGE( INITIAL 1 ,NEXT 1 ,MINEXTENTS 1 ,FILLFACTOR 0 , on CNDBA3), --299
PARTITION pN VALUES LESS THAN (MAXVALUE) STORAGE( INITIAL 1 ,NEXT 1 ,MINEXTENTS 1 ,FILLFACTOR 0 , on CNDBA4)
)
STORAGE( INITIAL 1 ,NEXT 1 , MINEXTENTS 1 ,FILLFACTOR 0)
;

select a.TABLE_NAME ,a.PARTITIONING_TYPE ,a.PARTITION_COUNT from ALL_PART_TABLES a ;

select table_name,partition_name,high_value ,ALL_TAB_PARTITIONS.TABLESPACE_NAME from all_tab_partitions;

begin
for i in 1…500
loop
INSERT into CNDBA.FANWEI_T1 values(i ,‘EEE’||i ) ;
commit ;
end loop ;
end;

commit ;

select count(*) from CNDBA.FANWEI_T1_PN ;

–创建 LIST 分区表

drop table if EXISTS CNDBA.list_sales ;

create table CNDBA.list_sales(
sales_id int ,
salman char(20),
saledate datetime,
city char(10)
)
partition by list(city)(
partition p1 values (‘合肥’, ‘巢湖’) STORAGE (on CNDBA1),
partition p2 values (‘安庆’, ‘怀宁’, ‘太湖’) STORAGE (on CNDBA2),
partition p3 values (‘广州’, ‘深圳’) STORAGE (on CNDBA3),
partition p_default values (default) STORAGE(on CNDBA4)
);

–drop table CNDBA.SALES ;

insert into CNDBA.LIST_SALES (sales_id,city) VALUES (1,‘合肥’) ;
insert into CNDBA.LIST_SALES (sales_id,city) VALUES (2,‘安庆’) ;
insert into CNDBA.LIST_SALES (sales_id,city) VALUES (3,‘广州’) ;
insert into CNDBA.LIST_SALES (sales_id,city) VALUES (4,‘aa’) ;
insert into CNDBA.LIST_SALES (sales_id,city) VALUES (5,‘北京’) ;
commit;

select T.OWNER, table_name,partitioning_type ,partition_count from all_part_tables t WHERE t.TABLE_NAME = upper(‘list_sales’);

select table_name,partition_name,high_value, TABLESPACE_NAME
from all_tab_partitions where TABLE_NAME = upper(‘list_sales’);

–创建哈希分区表

drop table if EXISTS CNDBA.hash_SALES01 ;

create table cndba.hash_sales01(
sales_id int,
saleman char(20),
saledate datetime,
city char(10)
)
partition by hash(city)(
partition p1 STORAGE( on CNDBA1),
partition p2 STORAGE (on CNDBA2),
partition p3 STORAGE (on CNDBA3),
partition p4 STORAGE(on CNDBA4)
)
;

drop table if EXISTS CNDBA.hash_SALES02 ;

Create table cndba.hash_sales02(
id int,
name varchar(20)
)
partition by hash (id)
partitions 3 STORE in (CNDBA1,CNDBA2,CNDBA3,CNDBA4);
–分区数量不大于表空间数数匹配

select table_name,partitioning_type ,partition_count from all_part_tables t;

select table_name,partition_name,high_value,H,TABLESPACE_NAME from all_tab_partitions
where table_name like ‘HASH%’;

–创建组合分区表

drop table if EXISTS CNDBA.SALES ;

create table cndba.sales(
sales_id int,
saleman char(20),
saledate datetime,
city char(10)
)
partition by list(city)
subpartition by range(saledate) subpartition template(
subpartition p11 values less than (‘2012-04-01’),
subpartition p12 values less than (‘2012-07-01’),
subpartition p13 values less than (‘2012-10-01’),
subpartition p14 values equ or less than (maxvalue))
(
partition p1 values (‘北京’, ‘天津’)
(
subpartition p11_1 values less than (‘2012-10-01’),
subpartition p11_2 values equ or less than (maxvalue)
),
partition p2 values (‘上海’, ‘南京’, ‘杭州’),
partition p3 values (default)
);

select table_name,partitioning_type ,partition_count from all_part_tables t where t.TABLE_NAME = ‘SALES’;

select table_name,partition_name,high_value,subpartition_count from all_tab_partitions where table_name=‘SALES’;

–DM 支持最多八层多级分区
–三级分区的例子：
drop TABLE If EXISTS cndba.student ;

create table cndba.student(
name varchar(20),
age int,
sex varchar(10) check (sex in (‘mail’,‘femail’)),
grade int check (grade in (7,8,9))
)
partition by list(grade)
subpartition by list(sex) subpartition template
(
subpartition q1 values(‘mail’),
subpartition q2 values(‘femail’)
),subpartition by range(age) subpartition template
(
subpartition r1 values less than (12),
subpartition r2 values less than (15),
subpartition r3 values less than (maxvalue)
)
(
partition p1 values (7),
partition p2 values (8),
partition p3 values (9)
);

select table_name,partitioning_type ,partition_count from all_part_tables t where table_name=‘STUDENT’;

select table_name,partition_name,high_value,subpartition_count from all_tab_partitions where table_name=‘STUDENT’;

–创建间隔分区
–如果用了间隔函数做分区，在数据插入的时候，如果没有适合的分区，数据库分自动给你创建一个新的分区。

drop table if EXISTS cndba.t_interval_year ;

create table cndba.t_interval_year (
employee_id int ,
empoyee_name VARCHAR(20),
bir date
) PARTITION BY RANGE (bir) INTERVAL
(
numtoyminterval(1,‘YEAR’)
)
(
PARTITION P1990 VALUES LESS THAN (TO_DATE(‘1991-01-01’,‘YYYY-MM-DD’)) STORAGE(on CNDBA1),
PARTITION P1991 VALUES LESS THAN (TO_DATE(‘1992-01-01’,‘YYYY-MM-DD’))STORAGE(on CNDBA2),
PARTITION P1992 VALUES LESS THAN (TO_DATE(‘1993-01-01’,‘YYYY-MM-DD’))STORAGE(on CNDBA3),
PARTITION P1993 VALUES LESS THAN (TO_DATE(‘1994-01-01’,‘YYYY-MM-DD’))STORAGE(on CNDBA4)
) ;

select table_name,partitioning_type ,partition_count from all_part_tables t where table_name=‘T_INTERVAL_YEAR’;

select table_name,partition_name,high_value from all_tab_partitions where table_name=‘T_INTERVAL_YEAR’;

INSERT INTO cndba.T_INTERVAL_YEAR VALUES(1,‘dave’,‘1989-10-24’);
INSERT INTO cndba.T_INTERVAL_YEAR VALUES(2,‘cndba’,‘1993-10-24’);
INSERT INTO cndba.T_INTERVAL_YEAR VALUES(3,‘hefei’,‘1994-10-24’);
INSERT INTO cndba.T_INTERVAL_YEAR VALUES(4,‘tianji’,‘1995-11-24’);

select table_name,partition_name,high_value, TABLESPACE_NAME from all_tab_partitions where table_name=‘T_INTERVAL_YEAR’;

–ROLLBACK ;
select * from CNDBA.T_INTERVAL_YEAR ;
commit;
–分区重命名
alter table CNDBA.T_INTERVAL_YEAR RENAME PARTITION SYS_P1256_1261 TO P1995 ;

–维护水平分区表

/*
增加分区
删除分区
合并分区：将相邻的两个列表和范围分区合并为一个分区。只能在范围分区上进行合并分区。
拆分分区：将某一个列表和范围分区拆分为相邻的两个分区。只能在范围分区上进行拆分分区；
交换分区：将分区数据跟普通表数据交换功能，普通表必须跟分区表同构（拥有相同的列和索引）。不支持含有加密列的分区表交换分区。
*/

–增加分区 –

–范围表 如果有maxvalue ,不允许添加分区,可以通过拆分分区
select table_name,partition_name,high_value from all_tab_partitions where table_name=‘FANWEI_T1’;

alter table CNDBA.FANWEI_T1 add PARTITION p4 VALUES less THAN (400) STORAGE (on cndba1) ;

–alter table CNDBA.FANWEI_T1 DROP PARTITION pN ;

alter table CNDBA.FANWEI_T1 add PARTITION pn VALUES LESS THAN (maxvalue) ;

–列表 在 DEFAULT 分区已存在时无法添加分区 ,拆分分区
select table_name,partition_name,high_value,TABLESPACE_NAME from all_tab_partitions where table_name=‘LIST_SALES’;

alter table CNDBA.LIST_SALES ADD PARTITION p4 VALUES (‘北京’,‘上海’) STORAGE (on cndba1) ;

–alter table CNDBA.LIST_SALES drop PARTITION P_DEFAULT ;
alter table CNDBA.LIST_SALES ADD PARTITION p_default VALUES (DEFAULT) STORAGE (on cndba4) ;

–删除分区
–范围
select table_name,partition_name,high_value from all_tab_partitions where table_name like ‘FANWEI%’;

alter table CNDBA.FANWEI_T1 drop PARTITION p4 ;

–list
select table_name,partition_name,high_value from all_tab_partitions where table_name like ‘LIST%’;

alter table CNDBA.LIST_SALES drop PARTITION p4 ;

–合并分区
–仅范围,列表分区表支持合并分区，并且合并的分区必须是范围相邻的两分区。
–范围
select table_name ,partition_name,high_value,TABLESPACE_NAME from all_tab_partitions where table_name like ‘FANWEI%’;

alter table cndba.FANWEI_T1 merge partitions p1, p2 into partition p1_2;

–list
select table_name ,partition_name,high_value,TABLESPACE_NAME from all_tab_partitions where table_name like ‘LIST%’;

alter table CNDBA.LIST_SALES MERGE PARTITIONS p2 ,p1 INTO PARTITION p2_1 ;

–拆分分区 : 范围,list
–范围
select table_name,partition_name,high_value,TABLESPACE_NAME from all_tab_partitions where table_name like ‘FANWEI%’;

alter table cndba.FANWEI_T1 split partition p1_2 at(100) into (partition p1,partition p2);

–maxvalue
select max(id) ,min(id) from CNDBA.FANWEI_T1_PN ;

alter table cndba.FANWEI_T1 split partition pN at(400) into (partition p4 STORAGE (on cndba1),partition pN);

–LIST
select table_name ,partition_name,high_value,TABLESPACE_NAME from all_tab_partitions where table_name like ‘LIST%’;

alter table cndba.LIST_SALES split partition P2_1 VALUES (‘合肥’,'太湖 ') into
(partition p1 STORAGE (ON CNDBA1) ,
partition P2 STORAGE (ON CNDBA2)
);

–default
select * from CNDBA.LIST_SALES_P_DEFAULT ;

alter table cndba.LIST_SALES split partition P_DEFAULT VALUES (‘北京’,‘上海’) into --‘上海’
(partition p4 STORAGE (ON CNDBA1) ,
partition P_DEFAULT STORAGE (ON CNDBA2)
);

select * from CNDBA.LIST_SALES_P4 ;

–交换分区-- 范围 ,list
/*
相同的表结构,索引,相同的分布方式.
几乎不涉及io,效率高.
*/

–范围
drop table if EXISTS cndba.jiaohuan_cndba ;

create table cndba.jiaohuan_cndba(
name char(15),
salary int)
partition by range(salary)(
partition p1 values less than (5000),
partition p2 values less than (10000),
partition p3 values less than (15000),
partition p5 values equ or less than (maxvalue)
);

drop table if EXISTS cndba.cndba_tmp ;
create table cndba.cndba_tmp(
name char(15),
salary int
)
;

insert into cndba.cndba_tmp values(‘dave’,8888);
commit ;

select * from CNDBA.JIAOHUAN_CNDBA_P2 ;
select * from CNDBA.CNDBA_TMP ;

alter table CNDBA.JIAOHUAN_CNDBA exchange partition p2 with table CNDBA.CNDBA_TMP;

delete from CNDBA.JIAOHUAN_CNDBA_P2 ;
commit ;
alter table CNDBA.JIAOHUAN_CNDBA exchange partition p3 with table CNDBA.CNDBA_TMP;

select * from CNDBA.JIAOHUAN_CNDBA_P3 ;