Oracle数据库分区--实例
--1.首先为每个散列子分区创建各自的表空间create tablespace hashTab1
datafile 'E:\app\Administrator\admin\orcl\hashDataFile01.dbf' size 50M;
create tablespace hashTab2
datafile 'E:\app\Administrator\admin\orcl\hashDataFile02.dbf' size 50M;
create tablespace hashTab3
datafile 'E:\app\Administrator\admin\orcl\hashDataFile03.dbf' size 50M;
--2.然后向用户credit授予表空间配额
alter user credit quota 50M on hashTab1;
alter user credit quota 50M on hashTab2;
alter user credit quota 50M on hashTab3;
--3.创建表consume2018
drop table credit.credit2018;
create table credit.credit2018
(
consume_id number,
card_no varchar2(50),
shop varchar2(50),
goods varchar2(50),
amount number(10,2),
consume_date date
)
partition by range(consume_date)
subpartition by hash(consume_id)
subpartitions 3 store in (hashTab1,hashTab2,hashTab3)
(
partition partition1 values less than(to_date('2018-02-01','yyyy-mm-dd')) tablespace creditTab1,
partition partition2 values less than(to_date('2018-03-01','yyyy-mm-dd')) tablespace creditTab2,
partition partition3 values less than(to_date('2018-04-01','yyyy-mm-dd')) tablespace creditTab3,
partition partition4 values less than(to_date('2018-05-01','yyyy-mm-dd')) tablespace creditTab4,
partition partition5 values less than(to_date('2018-06-01','yyyy-mm-dd')) tablespace creditTab5,
partition partition6 values less than(to_date('2018-07-01','yyyy-mm-dd')) tablespace creditTab6,
partition partition7 values less than(to_date('2018-08-01','yyyy-mm-dd')) tablespace creditTab7,
partition partition8 values less than(to_date('2018-09-01','yyyy-mm-dd')) tablespace creditTab8,
partition partition9 values less than(to_date('2018-10-01','yyyy-mm-dd')) tablespace creditTab9,
partition partition10 values less than(to_date('2018-11-01','yyyy-mm-dd')) tablespace creditTab10,
partition partition11 values less than(to_date('2018-12-01','yyyy-mm-dd')) tablespace creditTab11,
partition partition12 values less than(maxvalue) tablespace creditTab12
);
页:
[1]