Oracle 分区建表以及拆分分区

最新推荐文章于 2024-01-24 15:03:07 发布

zzx3q

最新推荐文章于 2024-01-24 15:03:07 发布

阅读量6.6k

点赞数

分类专栏： Oracle 文章标签： oracle calendar date table less access

本文链接：https://blog.csdn.net/zzx3q/article/details/7267129

版权

Oracle 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

--drop table
drop table bcr_tmp_cust_id;
--create table
create table BCR_TMP_CUST_ID
(
CUST_ID    VARCHAR2(40),
GUID       VARCHAR2(100),
ID_TYPE    VARCHAR2(40),
ID_VALUE   VARCHAR2(40),
IS_INVALID VARCHAR2(1),
CREATE_DT DATE,
CREATE_BY VARCHAR2(40),
UPDATE_DT DATE,
UPDATE_BY VARCHAR2(40),
CUST_TYPE VARCHAR2(20)
)
partition by range (CREATE_DT)
(
partition P_201201 values less than (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace APP_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
partition P_201202 values less than (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace APP_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
partition P_MAX values less than (MAXVALUE)
    tablespace APP_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )
);
comment on column BCR_TMP_CUST_ID.ID_TYPE
is 'AZID, DDD, IMDR';
comment on column BCR_TMP_CUST_ID.IS_INVALID
is 'Y or N';
comment on column BCR_TMP_CUST_ID.CUST_TYPE
is 'Customer Class, HCP or HCA';
create index IDX_TMP_ID_TBL_GUID on BCR_TMP_CUST_ID (GUID)
tablespace APP_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
create index IDX_TMP_ID_TBL_IDTYPE on BCR_TMP_CUST_ID (ID_TYPE)
tablespace APP_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
create index IDX_TMP_ID_TBL_IDVALUE on BCR_TMP_CUST_ID (ID_VALUE)
tablespace APP_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
--grant access
grant all on BCR_TMP_CUST_ID to hcp_bcr_dev ;
--alter nologging;
alter table BCR_TMP_CUST_ID nologging;
--insert data
insert /*+append*/ into BCR_TMP_CUST_ID
select * from bcr_tmp_cust_id_0217;

--add partition in bcr_tmp_cust_id;
alter table BCR_TMP_CUST_ID split partition P_MAX at (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
into (partition p_201203, partition p_left);