创建按季度分区的表

 

-- 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;