create tablespace part0 datafile ‘/data/oracle/oradata/orcl/part0.dbf’
size 50M
autoextend on next 5M ;
create tablespace part1 datafile ‘/data/oracle/oradata/orcl/part1.dbf’
size 50M
autoextend on next 5M;
create tablespace part2 datafile ‘/data/oracle/oradata/orcl/part2.dbf’
size 50M
autoextend on next 5M;
create tablespace part3 datafile ‘/data/oracle/oradata/orcl/part3.dbf’
size 50M
autoextend on next 5M;
create tablespace part4 datafile ‘/data/oracle/oradata/orcl/part4.dbf’
size 50M
autoextend on next 5M;
create tablespace part5 datafile ‘/data/oracle/oradata/orcl/part5.dbf’
size 50M
autoextend on next 5M;

select username from all_users;

create user userpart identified by 123456
default tablespace part0;
grant connect,resource to userpart;

alter user userpart quota unlimited on part1;
alter user userpart quota unlimited on part2;
alter user userpart quota unlimited on part3;
alter user userpart quota unlimited on part4;
alter user userpart quota unlimited on part5;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES100)/A.BYTES “% USED”,(C.BYTES100)/A.BYTES “% FREE”

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME

–其中,enable row movement 是可能遇到 update操作导致数据必须移动

create table pt_range_process(
pid number(10),
pname varchar2(30)
) partition by range(pid)(
partition p1 values less than(1000) tablespace part1,
partition p2 values less than(2000) tablespace part2,
partition p3 values less than(maxvalue) tablespace part3
) enable row movement;
– maxvalue 表示最大值
– 注意 than 后面必须有圆括号

insert into pt_range_process values(1,’less then 1000’);
insert into pt_range_process values(1003,’less then 1000’);
insert into pt_range_process values(2003,’less then 1000’);
insert into pt_range_process values(9000,’less then 1000’);

– 全量
select * from pt_range_process
– 分区2
select * from pt_range_process partition (p2);
–从分区2查找大于2000的数据,空

select * from pt_range_process partition (p2) where pid>2000

时间分区，和上面是一样的

create table pt_range_process_tm(
pid number(10),
pdate date
) partition by range(pdate)(
partition p1 values less than( to_date(‘2020-01-01’,’YYYY-MM-DD’) ) tablespace part1,
partition p2 values less than ( to_date(‘2024-01-01’,’YYYY-MM-DD’)) tablespace part2,
partition p3 values less than(maxvalue) tablespace part3
) enable row movement;

insert into pt_range_process_tm values (1, to_date(‘2010-01-01’,’YYYY-MM-DD’));
insert into pt_range_process_tm values (2, to_date(‘2030-01-01’,’YYYY-MM-DD’));
insert into pt_range_process_tm values (3, to_date(‘2040-01-01’,’YYYY-MM-DD’));
insert into pt_range_process_tm values (4, to_date(‘2022-01-01’,’YYYY-MM-DD’));

select * from pt_range_process_tm partition(p3);

create table pt_list_process(
pid number(10),
pname varchar2(30),
sex varchar2(10)
) partition by list(sex)(
partition p1 values (‘MAN’, ‘男’) tablespace part4,
partition p2 values (‘WOMAN’, ‘女’) tablespace part5,
partition p3 values (default) tablespace part2
) enable row movement;

insert into pt_list_process (pid, pname, sex) values (1, ‘瑶瑶’, ‘男’);
insert into pt_list_process (pid, pname, sex) values (2, ‘倩倩’, ‘WOMAN’);
insert into pt_list_process (pid, pname, sex) values (3, ‘优优’, ‘GOD’);
commit;

select * from pt_list_process partition(p3);

–
create table pt_hash_test(
pid number(10),
pname varchar2(30)
) partition by hash(pid)(
partition p1 tablespace tetstbs1,
partition p2 tablespace tetstbs2,
partition p3 tablespace tetstbs3,
partition p4 tablespace tetstbs4,
);
– 下面和上面一样

create table pt_hash_process(
pid number(10),
pname varchar2(30)
) partition by hash(pid)
partitions 4 store in (part1, part2, part3, part4);

insert into pt_hash_process values ( 1,’unknow 1’);
insert into pt_hash_process values ( 2,’unknow 2’);
insert into pt_hash_process values ( 3,’unknow 3’);
insert into pt_hash_process values ( 4,’unknow 4’);
insert into pt_hash_process values ( 5,’unknow 5’);

– 第二种形式没定义名字，不好使用

select * from pt_hash_process partition(p1);

create table pt_range_list_test(
pid number(10),
pname varchar2(30),
sex varchar2(10),
create_date date
) partition by range(create_date)
subpartition by list(sex)(
partition p1 values less than(to_date(‘2020-01-01’, ‘YYYY-MM-DD’)) tablespace part0(
subpartition sub1p1 values(‘MAN’) tablespace part3,
subpartition sub2p1 values(‘WOMAN’) tablespace part4,
subpartition sub3p1 values(default) tablespace part5
),
partition p2 values less than(to_date(‘2021-01-01’, ‘YYYY-MM-DD’)) tablespace part1(
subpartition sub1p2 values(‘MAN’) tablespace part3,
subpartition sub2p2 values(‘WOMAN’) tablespace part4,
subpartition sub3p2 values(default) tablespace part5
),
partition p3 values less than(maxvalue) tablespace part0(
subpartition sub1p3 values(‘MAN’) tablespace part3,
subpartition sub2p3 values(‘WOMAN’) tablespace part4,
subpartition sub3p3 values(default) tablespace part5
)
) enable row movement;

create table pt_interval_test(

pid number(10),
pname varchar2(30),
create_date date
) partition by range(create_date)
interval(numtoyminterval(1, ‘YEAR’))(
partition p1 values less than(to_date(‘2020-01-01’, ‘YYYY-MM-DD’)) tablespace part0
);

insert into pt_interval_test(pid, pname, create_date) values(1, ‘瑶瑶’, to_date(‘2019-01-01’, ‘YYYY-MM-DD’));
insert into pt_interval_test(pid, pname, create_date) values(2, ‘倩倩’, to_date(‘2020-01-01’, ‘YYYY-MM-DD’));

select * from user_tab_partitions t where t.table_name = upper(‘pt_interval_test’);
select * from user_tab_partitions t;
create table pt_virtual_test(
pid number(10),
pname varchar2(30),
create_date date,
create_quarterly as (to_char(create_date,’D’)) virtual
) partition by list(create_quarterly)(
partition p1 values(1) tablespace part0,
partition p2 values(2) tablespace part1,
partition p3 values(3) tablespace part2,
partition p4 values(4) tablespace part3,
partition p5 values(default) tablespace part4
); – 成功