oracle添加分区

实例一

create table test
(
  msg_id      VARCHAR2(16),
  result      INTEGER,
  ts          VARCHAR2(17),
  ts_time     TIMESTAMP(6),
  insert_time DATE
)
partition by range (TS_TIME)
(
  partition P20180110 values less than (TIMESTAMP' 2018-01-11 00:00:00')
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )
);

方式一

alter table test add partition P20180111 values less than (TIMESTAMP '2018-01-12 00:00:00')

方式二

alter table test add  partition P20180112 values less than (to_date('20180113 00:00:00','yyyymmdd hh24:mi:ss'));

最终查看建表语句如下

create table TEST
(
  msg_id      VARCHAR2(16),
  result      INTEGER,
  ts          VARCHAR2(17),
  ts_time     TIMESTAMP(6),
  insert_time DATE
)
partition by range (TS_TIME)
(
  partition P20180110 values less than (TIMESTAMP' 2018-01-11 00:00:00')
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20180111 values less than (TIMESTAMP' 2018-01-12 00:00:00')
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255,
  partition P20180112 values less than (TIMESTAMP' 2018-01-13 00:00:00')
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
);

实例二

create table test2
(
  msg_id      VARCHAR2(16),
  result      INTEGER,
  ts          VARCHAR2(17),
  ts_time     TIMESTAMP(6),
  insert_time DATE
)
partition by range (TS_TIME)
(
  partition P20180110 values less than (TIMESTAMP' 2018-01-11 00:00:00.000')
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )
);

通过plsql developer查看建表语句

-- Create table
create table TEST2
(
  msg_id      VARCHAR2(16),
  result      INTEGER,
  ts          VARCHAR2(17),
  ts_time     TIMESTAMP(6),
  insert_time DATE
)
partition by range (TS_TIME)
(
  partition P20180110 values less than (TIMESTAMP' 2018-01-11 00:00:00')
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )
);

总结:

(1)若在创建日分区的时候使用的是精确到秒的timestamp类型,由于其值与date类型是一致的,因此oracle引擎会将其与date同等对待,但是最终的建分区语句使用的还是timestamp,即使你在添加分区时使用的是to_date,也即是说以最开始见分区时候的关键字(timestamp)为准;
(2)若在创建日分区的时候使用的是精确到毫秒的timestamp类型,由于timestamp ‘20180101 00:00:00.000’与timestamp ‘20180101 00:00:00’值是一样的因此,会将二者同等对待;但是在但是最终的建分区语句使用的还是”timestamp精确到秒”,即使你在添加分区时使用的是”timestamp精确到毫秒”,也即是说以最开始见分区时候的”timestamp精确到秒”为准;

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页