一直想用oracle 11g 的interval分区(间断分区),这需要配合分区改名才能让我们可以使用分区查询,因为自动分区出来的分区名会是SYSPxxx这样。。
下面直接代码
drop tablespace bank_data including contents and datafiles;
create tablespace bank_data
logging
datafile 'D:\bank.dbf'
size 512m
autoextend on
next 512m maxsize 2048m
extent management local;
drop sequence seq_topic;
alter table topic drop constraint ck_topic_classify;
alter table bank drop constraint ck_bank_subject;
alter table bank drop constraint ck_bank_status;
drop table topic purge;
drop table bank purge;
create table bank(
id varchar2(17) primary key,
district varchar2(30) not null,
subject number(1) default 1 not null constraint ck_bank_subject check(subject in(1,4)),
status number(1) default 0 not null constraint ck_bank_status check(status in(0,1)),
remark varchar2(300)
);
--1道路交通安全法律、法规和规章 2交通信号 3安全行车、文明驾驶基础知识 4机动车驾驶操作相关基础知识
--1违法行为综合判断与案例分析 2安全行车常识 3常见交通标志、标线和交通手势辨识 4驾驶职业道德和文明驾驶常识
--5恶劣气候和复杂道路条件下驾驶常识 6紧急情况下避险常识 7交通事故救护及常见危化品处理常识
create table topic(
tid number(6) primary key,
version varchar2(17) not null,
pubdate date not null,
classify number(2) default 11 not null constraint ck_topic_classify check(classify in(11,12,13,14,41,42,43,44,45,46,47)),
title varchar2(1500) not null,
answer varchar2(20) not null,
explain varchar2(1200),
image varchar2(30)
)
partition by range(pubdate)
interval (NUMTOYMINTERVAL (3, 'MONTH')) store in (bank_data)
subpartition by list(classify)
subpartition template(
subpartition s1 values (11,12,13,14),
subpartition s4 values (default)
)
(partition p_201604 values less than (TO_DATE ('04/01/2016', 'MM/DD/YYYY')) tablespace bank_data);
alter table topic add constraint fk_topic_bank_version foreign key(version) references bank(id);
create sequence seq_topic sta