创建按季度分区的表-凯发app官方网站

-- create table
-- drop table watch3;
create table watch3
(
id number not null,
car_no varchar2(20) not null,
car_speed number(8,2) not null,
watch_time date not null,
device_no varchar2(20),
clbz varchar2(1) default '0'
)
partition by range (watch_time)
(
partition part_before_2012_q1 values less than (to_date(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2012_q1 values less than (to_date(' 2012-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2012_q2 values less than (to_date(' 2012-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2012_q3 values less than (to_date(' 2012-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2012_q4 values less than (to_date(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2013_q1 values less than (to_date(' 2013-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2013_q2 values less than (to_date(' 2013-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2013_q3 values less than (to_date(' 2013-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2013_q4 values less than (to_date(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2014_q1 values less than (to_date(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2014_q2 values less than (to_date(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2014_q3 values less than (to_date(' 2014-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2014_q4 values less than (to_date(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2015_q1 values less than (to_date(' 2015-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2015_q2 values less than (to_date(' 2015-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2015_q3 values less than (to_date(' 2015-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2015_q4 values less than (to_date(' 2015-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2016_q1 values less than (to_date(' 2016-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2016_q2 values less than (to_date(' 2016-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2016_q3 values less than (to_date(' 2016-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2016_q4 values less than (to_date(' 2016-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2017_q1 values less than (to_date(' 2017-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2017_q2 values less than (to_date(' 2017-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2017_q3 values less than (to_date(' 2017-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2017_q4 values less than (to_date(' 2017-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2018_q1 values less than (to_date(' 2018-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2018_q2 values less than (to_date(' 2018-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2018_q3 values less than (to_date(' 2018-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2018_q4 values less than (to_date(' 2018-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2019_q1 values less than (to_date(' 2019-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2019_q2 values less than (to_date(' 2019-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2019_q3 values less than (to_date(' 2019-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2019_q4 values less than (to_date(' 2019-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2020_q1 values less than (to_date(' 2020-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2020_q2 values less than (to_date(' 2020-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2020_q3 values less than (to_date(' 2020-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2020_q4 values less than (to_date(' 2020-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2021_q1 values less than (to_date(' 2021-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2021_q2 values less than (to_date(' 2021-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2021_q3 values less than (to_date(' 2021-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_2021_q4 values less than (to_date(' 2021-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
),
partition part_others values less than (maxvalue)
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited
)
);

检查分区设置情况：

select p.* from user_tab_partitions p order by 1,p.partition_position;

阅读(2506) | 评论(0) | 转发(1) |

上一篇：em的一些脚本

下一篇：取消用户对表空间的限额

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们